package gov.nhso.prov.acc.apps.acc6report;

import java.io.IOException;
import java.io.OutputStream;
import java.io.PrintWriter;
import java.nio.charset.CodingErrorAction;
import java.sql.Connection;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.struts.taglib.html.RewriteTag;

import com.crystaldecisions.reports.common.enums.YearType;
import com.isud.core.ISUD;
import com.isud.core.ISUDFactory;
import com.isud.paging.PageBean;
import com.sap.tc.logging.Log;

import gov.nhso.prov.acc.beans.Acc67ExpenseCategoryBean;
import gov.nhso.prov.acc.beans.Acc6AllocateBean;
import gov.nhso.prov.acc.beans.Acc6ReportFundsStatusBean;
import gov.nhso.prov.acc.beans.Acc6ReportMoneyStatusBean;
import gov.nhso.prov.acc.beans.Acc6ReportOverviewBean;
import gov.nhso.prov.acc.beans.Acc6ReportProvinceSummaryBean;
import gov.nhso.prov.acc.beans.Acc6ReportSummaryBean;
import gov.nhso.prov.acc.beans.Acc6ReportSummaryExpenseBean;
import gov.nhso.prov.acc.core.ConfigBundle;
import gov.nhso.prov.acc.core.DBConnection;
import gov.nhso.prov.acc.utils.DateUtil;
import gov.nhso.prov.acc.utils.ELUtil;
import gov.nhso.prov.acc.utils.UserInfo;
import gov.nhso.prov.acc.utils.Utility;

@SuppressWarnings({"rawtypes","unchecked"})
public class ACC6REPORT extends Utility{
	public String url = "/apps/acc6report/ACC6REPORT";
	public String token = "";
	
	public ACC6REPORT(HttpServletRequest request, HttpServletResponse response) {
		
		this.message(request).clearMsg();
		String job = this.getAttr(request, "job");
		String reportId = this.getAttr(request, "id");
		
		// --------- Write Log ---------------------
		System.out.println("File name > ACC6REPORT" );
		System.out.println("Job name >  " + job.toString().length());
		System.out.println("id name > " + reportId.toString());
		
		if (job.equals("init")) {
			this.url = this.url + reportId + ".jsp";
			this.init(request);
		} else if (job.equals("ACC6REPORT001")) {
			//รายงานภาพรวม
			this.ACC6REPORT001(request, response);
			
			// this.ACC6REPORT001Test(request , response);
			
		} else if (job.equals("ACC6REPORT002")) {
			//รายงานสถานะเงินกองทุน
			this.ACC6REPORT002(request, response);
		} else if (job.equals("ACC6REPORT003")) {
			//รายงานสรุปยอดบัญชีแต่ละจังหวัด
			this.ACC6REPORT003(request, response);
		} else if (job.equals("ACC6REPORT004")) {
			//รายงานรายจ่ายในแต่ละจังหวัด
			this.ACC6REPORT004(request, response);
		} else if (job.equals("ACC6REPORT005")) {
			//รายงานสถานะเงินในระบบ
			this.ACC6REPORT005(request, response);
		} else if (job.equals("ACC6REPORT006")) {
			//รายงานยอดรวมรายจ่าย
			this.ACC6REPORT006(request, response);
		} else if (job.equals("ACC6REPORT007")) {
			//รายงานทะเบียนคุมเช็ค
			this.ACC6REPORT007(request, response);
		} else if (job.equals("ACC6REPORT008")) {
			//รายงานสถานะรายการ
			this.ACC6REPORT008(request, response);
		} else if (job.equals("ACC6REPORT009")) {
			//รายงานรายละเอียดตามหมวดการจ่าย
			this.ACC6REPORT009(request, response);
		} else if (job.equals("ACC6REPORT010")) {
			//รายละเอียดการจ่ายตามกองทุน
			this.ACC6REPORT010(request, response);
		} else if (job.equals("ACC6REPORT011")) {
			//รายงานตามอายุเงิน
			this.ACC6REPORT011(request, response);
		} else if (job.equals("ACC6REPORT012")) {
			//รายงานผลการโอนของ ธกส.
			this.ACC6REPORT012(request, response);
		} else if(job.equals("export")) { 
			// export data report12
			this.export(request, response);
		} else if(job.equals("ACC6REPORT013")) {
			this.ACC6REPORT013(request, response);
		} else if(job.equals("export013")) {
			this.export013(request, response);
		}
	}
	
	
	private void export013(HttpServletRequest request,
			HttpServletResponse response) {
		
		Connection conn = null;
		ISUD dao = null;
		
		try {
			
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			
			StringBuffer sb = new StringBuffer();
			
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			PageBean pageBean = new PageBean();
			String condition = "";

			String accountType =  "S"; // this.getAttr(request , "at"); // accountType
			String provinceId = this.getAttr(request, "provinceId");
			String transferDailyAcc = this.getAttr(request , "td");
			String month  = this.getAttr(request , "m"); 
			String year  = this.getAttr(request, "y");
			
			String yearTo =this.getAttr(request, "yTo");
			String monthTo = this.getAttr(request, "mTo");
			
			int monthIntBefor  =  0;
			int yearIntBefor  = 0;
			
			if(month.equals("01")){ 
				yearIntBefor = Integer.parseInt(year) - 1;
				monthIntBefor = 12;
				
				System.out.println(yearIntBefor + " <> " +  monthIntBefor);
			}
			
			// String monthBefor  = "" + (String.valueOf(monthIntBefor).length() == 1 ? "0"+monthIntBefor : monthIntBefor);
			// String yearBefor = String.valueOf(yearIntBefor);
			
			this.beanUtil().copyRequest2Bean(request, pageBean);
			
			
			List listAllocate = new ArrayList();
			
			
			if (provinceId != null && !provinceId.equals("")) {
				condition = " AND PV.PROVINCE_ID = '" + provinceId + "' ";
			}

 			pageBean.setOrderField(" NHSO_ZONE , PROVINCE_ID ");
			
			sb.append("SELECT * FROM  ( ");
			sb.append(" SELECT ");
			sb.append(" PV.NHSO_ZONE , PV.NHSO_ZONENAME, PV.PROVINCE_ID , PV.PROVINCE_NAME  , ");
			sb.append(" SUM( ");
			sb.append(" CASE WHEN ");
			sb.append(" TRANSFER_DAILY_KEY IN (SELECT MAX(TRANSFER_DAILY_KEY) "); 
			sb.append(" FROM ACC67_TRANSFER_DAILY  WHERE TRANSFER_DAILY_ACC='"+transferDailyAcc+"' AND ACCOUNT_TYPE='" + accountType + "' AND substr(TRANSFER_DATE ,1,6) < '" +  year + month + "' AND PROVINCE_ID = PV.PROVINCE_ID ");
			// sb.append(" GROUP BY  PROVINCE_ID ");
			// sb.append(" HAVING PROVINCE_ID = PV.PROVINCE_ID ");
			sb.append(" ) "); 
			sb.append(" THEN ATD.REMAIN ELSE 0 END ");
			sb.append(" ) AS ONE , ");
			
			sb.append(" SUM( ");
			sb.append(" CASE WHEN "); 
			sb.append(" ATD.TRANSFER_DAILY_ACC='"+transferDailyAcc+"' AND ACCOUNT_TYPE='" + accountType + "'  and ATD.transfer_type='C' and ");
			sb.append(" ( substr(ATD.transfer_date,1,6) >= '"+ year + month +"' and ");
			sb.append("  substr(ATD.transfer_date,1,6) <= '"+ yearTo + monthTo +"' ) ");
			sb.append(" THEN ATD.AMOUNT ELSE 0 END ");
			sb.append("  ) AS TWO  , ");
			
			sb.append(" SUM( ");
			sb.append(" CASE WHEN "); 
			sb.append(" TRANSFER_DAILY_ACC='"+transferDailyAcc+"' AND ACCOUNT_TYPE='" + accountType + "'  and transfer_type='D' and ");
			sb.append(" ( substr(ATD.transfer_date,1,6) >= '"+ year + month +"' and ");
			sb.append("  substr(ATD.transfer_date,1,6) <= '"+ yearTo + monthTo +"' ) ");
			sb.append(" THEN ATD.AMOUNT ELSE 0 END ");
			sb.append(" ) AS THREE , ");
			
			sb.append(" SUM( ");
			sb.append(" CASE WHEN ");
			sb.append(" transfer_daily_key IN (SELECT MAX(transfer_daily_key) ");
			sb.append(" FROM acc67_transfer_daily t WHERE TRANSFER_DAILY_ACC='"+transferDailyAcc+"' AND ACCOUNT_TYPE='" + accountType + "'  AND substr(transfer_date,1,6)  <= '" + yearTo + monthTo + "' AND PROVINCE_ID = PV.PROVINCE_ID");
			//sb.append(" GROUP BY  province_id ");
			//sb.append("  HAVING PROVINCE_ID = PV.PROVINCE_ID ");
			sb.append("  )  ");
			sb.append("  THEN ATD.REMAIN ELSE 0 END ");
			sb.append(" ) AS FOUR  ");
			
			sb.append(" FROM ACC67_TRANSFER_DAILY ATD ");
			sb.append(" INNER JOIN PROVINCE  PV ON ATD.PROVINCE_ID  =  PV.PROVINCE_ID ");
			sb.append(" WHERE  ");
			sb.append(" TRANSFER_DAILY_ACC='"+transferDailyAcc+"' AND ACCOUNT_TYPE='" + accountType + "'  ");
			sb.append(" AND PV.NHSO_ZONE NOT IN ('14' , '15' ) ");
			sb.append(" AND PV.PROVINCE_ID <> '1000' ");
			
			sb.append(condition);
			
			sb.append(" GROUP BY  ");
			sb.append("  PV.NHSO_ZONE , PV.NHSO_ZONENAME, PV.PROVINCE_ID , PV.PROVINCE_NAME ");
 			sb.append(" ORDER BY  PV.NHSO_ZONE ASC , PV.PROVINCE_ID ASC ");
 			
 			sb.append(" ) ");
 			
 			System.out.println("export sql  > " + sb.toString());
			
			listAllocate = dao.listDataSql(sb.toString());
			
			response.setContentType("text/vnd.ms-excel");
			response.setHeader("Content-Disposition","attachment; filename=\"Report013- " + (provinceId.equals("") ? "_All" : provinceId) + ".xls\"");
			response.setCharacterEncoding("windows-874");
			
			HSSFWorkbook wb = new HSSFWorkbook();
			HSSFSheet sheet = wb.createSheet("sheet 0");
			
			HSSFCellStyle cellStyle = wb.createCellStyle();
			cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			HSSFCellStyle cellStyleAlignRigth = wb.createCellStyle();
			cellStyleAlignRigth.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
			
			// Create Row
			HSSFRow rowHead = sheet.createRow((short)0);
			HSSFCell cell1Head = rowHead.createCell(0);
			HSSFCell cell2Head = rowHead.createCell(1);
			
			
			String strHeader =  "รายงานเงินสดคงเหลือ ";
			cell1Head.setCellValue(strHeader);
			cell2Head.setCellValue("ข้อมูลระหว่าง เดือน/ปี    " +  (month + "/" + year) +"      ถึง เดือน/ปี ที่ " + (  monthTo + "/" +  yearTo ));
			
			// Create Row
			HSSFRow rowHeadSub = sheet.createRow((short)1);
			HSSFCell cell1HeadSub = rowHeadSub.createCell(0);
			HSSFCell cell2HeadSub = rowHeadSub.createCell(1);
			
			SimpleDateFormat  forDateFormat  = new SimpleDateFormat("dd/MM/yyyy  hh:mm:ss");
		
			 cell1HeadSub.setCellValue(" พิมพ์ ณ วันที่ " +  forDateFormat.format(new Date()));
			
			
			HSSFRow row = sheet.createRow((short)2);
			HSSFCell cell1 = row.createCell(0);
			HSSFCell cell2 = row.createCell(1);
			HSSFCell cell3 = row.createCell(2);
			HSSFCell cell4 = row.createCell(3);
			HSSFCell cell5 = row.createCell(4);
			HSSFCell cell6 = row.createCell(5);
			HSSFCell cell7 = row.createCell(6);
			HSSFCell cell8 = row.createCell(7);
			
			sheet.setDefaultColumnWidth((short) 20);
			cell1.setCellStyle(cellStyle);
			cell2.setCellStyle(cellStyle);
			cell3.setCellStyle(cellStyle);
			cell4.setCellStyle(cellStyle);
			cell5.setCellStyle(cellStyle);
			cell6.setCellStyle(cellStyle);
			cell7.setCellStyle(cellStyle);
			cell8.setCellStyle(cellStyle);
			
			cell1.setCellValue("รหัสเขต");
			cell2.setCellValue("เขต");
			cell3.setCellValue("รหัสจังหวัด");
			cell4.setCellValue("จังหวัด");
			cell5.setCellValue("ยอดต้นเดือน");
			cell6.setCellValue("เงินรับสุทธิ");
			cell7.setCellValue("เงินจ่ายสุทธิ");
			cell8.setCellValue("ยอดปลายเดือน");
			
			for (int i = 1 ; i <= listAllocate.size();i++){
				
				System.out.println( " export :  " + i);
				
				Map map = new HashMap();
				map = (Map) listAllocate.get( i-1);
			
				row = sheet.createRow((short) (i + 2));
				cell1 = row.createCell(0);
				cell2 = row.createCell(1);
				cell3 = row.createCell(2);
				cell4 = row.createCell(3);
				cell5 = row.createCell(4);
				cell6 = row.createCell(5);
				cell7 = row.createCell(6);
				cell8 = row.createCell(7);
				
				cell1.setCellValue(dao.chkData(map.get("NHSO_ZONE")));
				cell2.setCellValue(dao.chkData(map.get("NHSO_ZONENAME")));
				cell3.setCellValue(dao.chkData(map.get("PROVINCE_ID")));
				cell4.setCellValue(dao.chkData(map.get("PROVINCE_NAME")));
				cell5.setCellValue(dao.chkData(map.get("ONE")));
				cell6.setCellValue(dao.chkData(map.get("TWO")));
				cell7.setCellValue(dao.chkData(map.get("THREE")));
				cell8.setCellValue(dao.chkData(map.get("FOUR")));
				
			}
				
			OutputStream out = response.getOutputStream();
			wb.write(out);
			out.close();
			conn.commit();
			
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}


	/**
	 * @aother Freealife
	 * @param request
	 * @param response
	 */
	private String ACC6REPORT013(HttpServletRequest request,
			HttpServletResponse response) {
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			PageBean pageBean = new PageBean();
			String condition = "";
			StringBuffer sb = new StringBuffer();
			String transferDailyAcc = this.getAttr(request, "transferDailyAcc");
			String accountType = "S"; //  this.getAttr(request, "accountType");
			String provinceId = this.getAttr(request, "provinceId");
			String showResult = this.getAttr(request, "showResult");
			String startDate = this.getAttr(request, "startDate");
			String endDate = this.getAttr(request, "endDate");
			String month = this.getAttr(request, "month");
			String year =  this.getAttr(request, "year");
	
			String monthTo = this.getAttr(request, "monthTo");
			String yearTo =  this.getAttr(request, "yearTo");
	
			int monthIntBefor  =  0;
			int yearIntBefor  = 0;
			
			if(month.equals("01")){ 
				yearIntBefor = Integer.parseInt(year) - 1;
				monthIntBefor = 12;
			}
			
			String monthBefor  = "" + (String.valueOf(monthIntBefor).length() == 1 ? "0"+monthIntBefor : monthIntBefor);
			String yearBefor = String.valueOf(yearIntBefor);
			
			this.beanUtil().copyRequest2Bean(request, pageBean);
			
			if (provinceId != null && !provinceId.equals("")) {
				condition = " AND PV.PROVINCE_ID = '" + provinceId + "' ";
			}

 			pageBean.setOrderField(" NHSO_ZONE , PROVINCE_ID ");
			
			sb.append("SELECT * FROM  ( ");
			sb.append(" SELECT ");
			sb.append(" PV.NHSO_ZONE , PV.NHSO_ZONENAME, PV.PROVINCE_ID , PV.PROVINCE_NAME  , ");
			
			sb.append(" SUM( ");
			sb.append(" CASE WHEN ");
			sb.append(" TRANSFER_DAILY_KEY IN (SELECT MAX(TRANSFER_DAILY_KEY) "); 
			sb.append(" FROM ACC67_TRANSFER_DAILY  WHERE TRANSFER_DAILY_ACC='"+transferDailyAcc+"' AND ACCOUNT_TYPE='" + accountType + "' AND substr(TRANSFER_DATE ,1,6) < '" +  year + month + "' AND PROVINCE_ID = PV.PROVINCE_ID ");
			// sb.append(" GROUP BY  PROVINCE_ID ");
			// sb.append(" HAVING PROVINCE_ID = PV.PROVINCE_ID ");
			sb.append(" ) "); 
			sb.append(" THEN ATD.REMAIN ELSE 0 END ");
			sb.append(" ) AS ONE , ");
			
			sb.append(" SUM( ");
			sb.append(" CASE WHEN "); 
			sb.append(" ATD.TRANSFER_DAILY_ACC='"+transferDailyAcc+"' AND ACCOUNT_TYPE='" + accountType + "'  and ATD.transfer_type='C' and ");
			sb.append(" ( substr(ATD.transfer_date,1,6) >= '"+ year + month +"' and ");
			sb.append("  substr(ATD.transfer_date,1,6) <= '"+ yearTo + monthTo +"' ) ");
			sb.append(" THEN ATD.AMOUNT ELSE 0 END ");
			sb.append("  ) AS TWO  , ");
			
			sb.append(" SUM( ");
			sb.append(" CASE WHEN "); 
			sb.append(" TRANSFER_DAILY_ACC='"+transferDailyAcc+"' AND ACCOUNT_TYPE='" + accountType + "'  and transfer_type='D' and ");
			sb.append(" ( substr(ATD.transfer_date,1,6) >= '"+ year + month +"' and ");
			sb.append("  substr(ATD.transfer_date,1,6) <= '"+ yearTo + monthTo +"' ) ");
			sb.append(" THEN ATD.AMOUNT ELSE 0 END ");
			sb.append(" ) AS THREE , ");
			
			sb.append(" SUM( ");
			sb.append(" CASE WHEN ");
			sb.append(" transfer_daily_key IN (SELECT MAX(transfer_daily_key) ");
			sb.append(" FROM acc67_transfer_daily t WHERE TRANSFER_DAILY_ACC='"+transferDailyAcc+"' AND ACCOUNT_TYPE='" + accountType + "'  AND substr(transfer_date,1,6)  <= '" + yearTo + monthTo + "' AND PROVINCE_ID = PV.PROVINCE_ID");
			//sb.append(" GROUP BY  province_id ");
			//sb.append("  HAVING PROVINCE_ID = PV.PROVINCE_ID ");
			sb.append("  )  ");
			sb.append("  THEN ATD.REMAIN ELSE 0 END ");
			sb.append(" ) AS FOUR  ");
			
			sb.append(" FROM ACC67_TRANSFER_DAILY ATD ");
			sb.append(" INNER JOIN PROVINCE  PV ON ATD.PROVINCE_ID  =  PV.PROVINCE_ID ");
			sb.append(" WHERE  ");
			sb.append(" TRANSFER_DAILY_ACC='"+transferDailyAcc+"' AND ACCOUNT_TYPE='" + accountType + "'  ");
			sb.append(" AND PV.NHSO_ZONE NOT IN ('14' , '15' ) ");
			sb.append(" AND PV.PROVINCE_ID <> '1000' ");
			
			sb.append(condition);
			
			sb.append(" GROUP BY  ");
			sb.append("  PV.NHSO_ZONE , PV.NHSO_ZONENAME, PV.PROVINCE_ID , PV.PROVINCE_NAME ");
 			sb.append(" ORDER BY  PV.NHSO_ZONE ASC , PV.PROVINCE_ID ASC ");
 			
 			sb.append(" ) ");
			
 			pageBean.setOrderType("ASC");
			
			// show  statement  SQl 
			System.out.println("Report 13  >> sb.toString() >> " + sb.toString());
			
			pageBean = dao.selectPage(sb.toString(), pageBean);
			if (pageBean.getPageData() == null) {
				this.message(request).setMsgCode("I001");
			}
			
			request.setAttribute("pageBean", pageBean);
			request.setAttribute("transferDailyAcc", transferDailyAcc);
			request.setAttribute("accountType", accountType);
			request.setAttribute("provinceId", provinceId);
			request.setAttribute("showResult", showResult);
			
			// request.setAttribute("startDate", this.getAttr(request, "startDate"));
			// request.setAttribute("endDate", this.getAttr(request, "endDate"));
			
			request.setAttribute("lsBudgetYear", this.dataUtil(dao).lsBudgetYear());
			request.setAttribute("year" , year);
			
			conn.commit();
			
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			
			e.printStackTrace();
			
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
			dao.printMessage();
			
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
				this.url = "/apps/acc6report/ACC6REPORT013.jsp";
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
		
	}

	/**
	 * Export data to excel 
	 * @author FreeAlife
	 * @date 2014-08-15
	 * @param request
	 * @param response
	 */
	private void export(HttpServletRequest request, HttpServletResponse response) {
		
		Connection conn = null;
		ISUD dao = null;
		
		try {
			
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			
			StringBuffer sb = new StringBuffer();
			
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			PageBean pageBean = new PageBean();
			String condition = "";
		
			String accountType = this.getAttr(request , "at"); // accountType
			String startDate = this.getAttr(request, "startDate");
			String endDate = this.getAttr(request, "endDate");
			String provinceId = this.getAttr(request, "provinceId");
			String transferDailyAcc = this.getAttr(request , "td");
			String month  = this.getAttr(request , "m"); 
			String year  = this.getAttr(request, "y");
			String showResult = this.getAttr(request, "showResult");
		
			List listAllocate = new ArrayList();
			
			this.beanUtil().copyRequest2Bean(request, pageBean);
			
			if (provinceId != null && !provinceId.equals("")) {
				condition = " AND PROVINCE_ID = '" + provinceId + "' ";
			}

			if (showResult.equals("2")) {
				pageBean.setOrderField("T2.PROVINCE_NAME || T2.TRANSFER_DAILY_KEY");
				sb.append("SELECT T2.* FROM ");
				sb.append("(SELECT MAX(TRANSFER_DAILY_KEY) AS TRANSFER_DAILY_KEY,PROVINCE_ID FROM " + this.schema + ".ACC67_TRANSFER_DAILY ");
				sb.append("WHERE SUBSTR(TRANSFER_DATE,5,2) = '" + month + "'");
				
				if(!year.equals("")){
					sb.append(" AND SUBSTR(TRANSFER_DATE,0,4) = '" + year + "'");
				}
				
				sb.append(" AND TRANSFER_DAILY_ACC = '"+transferDailyAcc+"' AND ACCOUNT_TYPE = '" + accountType + "'  GROUP BY PROVINCE_ID) ");
				sb.append("T1," + this.schema + ".ACC67_TRANSFER_DAILY T2 WHERE T1.TRANSFER_DAILY_KEY = T2.TRANSFER_DAILY_KEY ");
				sb.append("AND T2.TRANSFER_DAILY_ACC = '"+transferDailyAcc+"' AND T2.ACCOUNT_TYPE = '" + accountType + "' " + condition.replaceAll("PROVINCE_ID", "T2.PROVINCE_ID"));
			} else {
				pageBean.setOrderField("PROVINCE_NAME || TRANSFER_DAILY_KEY");
				startDate = this.dateUtil().changeFormatDate2YYYYMMDD(startDate, "/", "");
				endDate = this.dateUtil().changeFormatDate2YYYYMMDD(endDate, "/", "");
				sb.append("SELECT * FROM " + this.schema + ".ACC67_TRANSFER_DAILY ");
				sb.append("WHERE TRANSFER_DAILY_ACC = '"+transferDailyAcc+"' AND ACCOUNT_TYPE = '" + accountType + "' " + condition);
				sb.append(" AND TO_NUMBER(TRANSFER_DATE) >= TO_NUMBER(" + startDate + ") AND TO_NUMBER(TRANSFER_DATE) <= TO_NUMBER(" + endDate + ") ");
			}
		
			
			System.out.println("SQL_REPORT_012 export > " + sb.toString());
			
			listAllocate = dao.listDataSql(sb.toString());
			
			response.setContentType("text/vnd.ms-excel");
			response.setHeader("Content-Disposition","attachment; filename=\"Report012-" + (provinceId.equals("") ? "_All" : provinceId) + ".xls\"");
			response.setCharacterEncoding("windows-874");
			
			HSSFWorkbook wb = new HSSFWorkbook();
			HSSFSheet sheet = wb.createSheet("sheet 0");
			
			HSSFCellStyle cellStyle = wb.createCellStyle();
			HSSFCellStyle cellStyleNumber = wb.createCellStyle();
			HSSFDataFormat format = wb.createDataFormat();
			
			cellStyleNumber.setDataFormat(format.getFormat("#,##0.00"));
			cellStyleNumber.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
			
			cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			
			HSSFCellStyle cellStyleAlignRigth = wb.createCellStyle();
			cellStyleAlignRigth.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
			HSSFRow row = sheet.createRow((short)0);
			HSSFCell cell1 = row.createCell(0);
			HSSFCell cell2 = row.createCell(1);
			HSSFCell cell3 = row.createCell(2);
			HSSFCell cell4 = row.createCell(3);
			HSSFCell cell5 = row.createCell(4);
			HSSFCell cell6 = row.createCell(5);
			
			sheet.setDefaultColumnWidth((short) 35);
			cell1.setCellStyle(cellStyle);
			cell2.setCellStyle(cellStyle);
			cell3.setCellStyle(cellStyle);
			cell4.setCellStyle(cellStyle);
			cell5.setCellStyle(cellStyleNumber);
			cell6.setCellStyle(cellStyleNumber);
			
			cell1.setCellValue("จังหวัด");
			cell2.setCellValue("วันที่");
			cell3.setCellValue("Transaction Code");
			cell4.setCellValue("ประเภทการทำรายการ");
			cell5.setCellValue("จำนวนเงิน");
			cell6.setCellValue("คงเหลือ");
			
			ELUtil elUtil = new ELUtil();
			
			for (int i = 0;i < listAllocate.size();i++){
				
				Map map = new HashMap();
				map = (Map) listAllocate.get(i);
			
				row = sheet.createRow((short) (i + 1));
				cell1 = row.createCell(0);
				cell2 = row.createCell(1);
				cell3 = row.createCell(2);
				cell4 = row.createCell(3);
				cell5 = row.createCell(4);
				cell6 = row.createCell(5);
				
				cell1.setCellValue(dao.chkData(map.get("PROVINCE_NAME")));
				cell2.setCellValue(elUtil.getDataUtil("DAT" , dao.chkData(map.get("TRANSFER_DATE"))));
				cell3.setCellValue(dao.chkData(map.get("TRANSACTION_CODE")));
				cell4.setCellValue(dao.chkData(map.get("TRANSFER_TYPE")));
				cell5.setCellValue(dao.chkData(map.get("AMOUNT")));
				cell6.setCellValue(dao.chkData(map.get("REMAIN")));
			}
				
			OutputStream out = response.getOutputStream();
			wb.write(out);
			out.close();
			conn.commit();
			
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	
	}

	private String init (HttpServletRequest request) {
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			Acc67ExpenseCategoryBean expenseCategoryBean = new Acc67ExpenseCategoryBean();
			List listCategory = null;
			expenseCategoryBean.setCategoryAcc("6");
			expenseCategoryBean.setActive("Y");
			listCategory = dao.selectList(expenseCategoryBean);

			request.setAttribute("listCategory", listCategory);
			
			request.setAttribute("lsProvince", this.dataUtil(dao).lsProvince());
			request.setAttribute("lsBudgetYear", this.dataUtil(dao).lsBudgetYear());
			request.setAttribute("expenseStatus", this.properties().statusExpense());
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	
	/**
	 * @modify CEO FreeAlife
	 * @param request
	 * @param response
	 * @return String url
	 */
	private String ACC6REPORT001(HttpServletRequest request, HttpServletResponse response) {
		
		System.out.println("---- Process name ACC6REPORT001 -----");
		
		Connection conn = null;
		ISUD dao = null;
		
		try {
			
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			List ls = new ArrayList();
			UserInfo ui = this.user(request);
			Acc6ReportOverviewBean arob = new Acc6ReportOverviewBean();
			
			String provinceIdFilter = this.getAttr(request, "provinceIdFilter");
			String budgetYearFilter = this.getAttr(request, "budgetYearFilter"); 
			String startDate = this.getAttr(request, "startDateFilter");
			String endDate = this.getAttr(request, "endDateFilter");
			String condition = ""; //for budget Year <= 2556
			String condition2 = ""; // for budget Year > 2556
			//int startBudgetYear = 2;
			//int endBudgetYear = 1;
			StringBuffer sb = new StringBuffer();

			if (provinceIdFilter.equals("")) {
				provinceIdFilter = "all";
			}
			
//			if(budgetYearFilter.equals("")){ 
//				budgetYearFilter = "all";
//			}
			
			arob.setBudgetYearFilter(budgetYearFilter);
			arob.setProvinceIdFilter(provinceIdFilter);
			arob.setStartDateFilter(this.dateUtil().changeFormatDate2YYYYMMDD(startDate,"/",""));
			arob.setEndDateFilter(this.dateUtil().changeFormatDate2YYYYMMDD(endDate,"/",""));
			arob.setUserId(ui.getUserId());
			sb.append("DELETE FROM "+this.schema+".ACC6_REPORT_OVERVIEW WHERE USER_ID = '" + ui.getUserId() + "' ");
			dao.execDataSql(sb.toString());
			condition = ""; // for budget Year <= 2556
			condition2 = ""; // for budget Year > 2556

			// Check Deploy On Product Or Development.
			if (ConfigBundle.getValue("DEPLOY_TYPE").equals("P")) {
				startDate = this.dateUtil().changeDateToDB(startDate);
				endDate = this.dateUtil().changeDateToDB(endDate);
			} else {
				startDate =  this.dateUtil().changeFormatDate2YYYYMMDD(startDate, "/", "");
				endDate = this.dateUtil().changeFormatDate2YYYYMMDD(endDate, "/", "");
			}
			
			String budgetYear = budgetYearFilter;
			
			int iBudgetYear = Integer.parseInt(budgetYear);
					
			if (ConfigBundle.getValue("DEPLOY_TYPE").equals("P")) {
				condition = " AND TO_DATE(INCOME_DATE,'DD.MM.YYYY') >= " + startDate + " AND TO_DATE(INCOME_DATE,'DD.MM.YYYY') <= " + endDate + " ";
			    condition2 = " AND TO_DATE(INCOME_DATE,'DD.MM.YYYY') >= TO_DATE('01/10/2012','DD.MM.YYYY') AND TO_DATE(INCOME_DATE,'DD.MM.YYYY') <= TO_DATE('30/09/2013','DD.MM.YYYY') ";
			} else {
				condition = " AND TO_CHAR(INCOME_DATE,'YYYYMMDD') >= " + startDate + " AND TO_CHAR(INCOME_DATE,'YYYYMMDD') <= " + endDate + " ";
				condition2 = " AND TO_CHAR(INCOME_DATE,'YYYYMMDD') >= '20121001' AND TO_CHAR(INCOME_DATE,'YYYYMMDD') <= '20130930' ";
			}
					
					sb.setLength(0);
					sb.append("INSERT INTO " + this.schema + ".ACC6_REPORT_OVERVIEW (USER_ID,PROVINCE_ID_FILTER,BUDGET_YEAR_FILTER,START_DATE_FILTER,");
					sb.append("END_DATE_FILTER,PROVINCE_ID,PROVINCE_NAME,ALLOCATE_CODE,ALLOCATE_NAME,SUM_INCOME_AMOUNT_TYPE_B,SUM_INCOME_AMOUNT_TYPE_S,SUM_EXPENSE_AMOUNT) ");
					
					// Main call data
					sb.append(" SELECT "); 
					sb.append(" USER_ID , PROVINCE_ID_FILTER  , BUDGET_YEAR_FILTER  , START_DATE_FILTER , END_DATE_FILTER , PROVINCE_ID , PROVINCE_NAME , ");
					sb.append(" ALLOCATE_CODE ,  ALLOCATE_NAME , ");
					sb.append(" SUM(SUM_INCOME_AMOUNT_TYPE_B) , SUM(SUM_INCOME_AMOUNT_TYPE_S) , SUM(SUM_EXPENSE_AMOUNT) ");
					sb.append(" FROM"); 
					sb.append(" ( ");  
					
					sb.append(" SELECT "); 
					sb.append(" '" + ui.getUserId() + "' AS USER_ID,'" + arob.getProvinceIdFilter() + "' AS PROVINCE_ID_FILTER , ACC6_ALLOCATE.BUDGET_YEAR AS BUDGET_YEAR_FILTER , '" + arob.getStartDateFilter() + "' AS START_DATE_FILTER  , '" + arob.getEndDateFilter() + "' AS END_DATE_FILTER , ");
					sb.append(" PROVINCE_ID  ,  PROVINCE_NAME  ,  ACC6_ALLOCATE.ALLOCATE_CODE  ,  ACC6_ALLOCATE.ALLOCATE_NAME , ");
					sb.append(" 0 AS SUM_INCOME_AMOUNT_TYPE_B , 0 AS SUM_INCOME_AMOUNT_TYPE_S , 0 AS SUM_EXPENSE_AMOUNT  ");
					sb.append(" FROM  " + this.schema + ".ACC6_ALLOCATE ,  " + this.schema + ".PROVINCE  ");
					sb.append(" WHERE ALLOCATE_LEVEL = '1' ");
				  	
					//Condition Other
					/*
					 if (!budgetYearFilter.equals("all")) {
						sb.append(" AND ACC6_ALLOCATE.BUDGET_YEAR <= '" + budgetYear + "'");
					}
					*/
					
					if (!provinceIdFilter.equals("all") || ui.getRoleId().equals("3")){	
						sb.append(" AND PROVINCE.PROVINCE_ID = '" + provinceIdFilter + "' ");
					} else if (ui.getRoleId().equals("2") && provinceIdFilter.equals("all")) {
						sb.append(" AND PROVINCE.NHSO_ZONE = '" + ui.getAreaCode() + "' ");
					}
					
					sb.append(" UNION ALL ");  

					sb.append(" SELECT ");
					sb.append(" USER_ID , PROVINCE_ID_FILTER  , BUDGET_YEAR_FILTER  , START_DATE_FILTER , END_DATE_FILTER , PROVINCE_ID , PROVINCE_NAME ,");
					sb.append(" ACC6_ALLOCATE.ALLOCATE_CODE  ,  ACC6_ALLOCATE.ALLOCATE_NAME , SUM_INCOME_AMOUNT_TYPE_B , SUM_INCOME_AMOUNT_TYPE_S , SUM_EXPENSE_AMOUNT ");
					sb.append(" FROM ACC6_ALLOCATE ");
					
					sb.append(" INNER JOIN "); 
					sb.append(" ( ");
							
					sb.append(" SELECT "); 
					sb.append(" USER_ID , PROVINCE_ID_FILTER  , BUDGET_YEAR_FILTER  , START_DATE_FILTER , END_DATE_FILTER , PROVINCE_ID , PROVINCE_NAME , ");
					sb.append(" SUBSTR(ALLOCATE_CODE , 1 , 4) AS ALLOCATE_CODE  , ");
					sb.append(" SUM(SUM_INCOME_AMOUNT_TYPE_B) AS SUM_INCOME_AMOUNT_TYPE_B ,  SUM(SUM_INCOME_AMOUNT_TYPE_S + SUM_INCOME_AMOUNT) AS SUM_INCOME_AMOUNT_TYPE_S , ");
					sb.append(" SUM(SUM_INCOME_AMOUNT_TYPE_C + SUM_INCOME_AMOUNT_TYPE_T + SUM_INCOME_AMOUNT_TYPE_P) AS SUM_EXPENSE_AMOUNT ");
					sb.append(" FROM  ( ");
							  
					sb.append(" SELECT "); 
					sb.append("'" + ui.getUserId() + "' AS USER_ID , '" + arob.getProvinceIdFilter() + "' AS PROVINCE_ID_FILTER, ACCALL.BUDGET_YEAR AS BUDGET_YEAR_FILTER,'" + arob.getStartDateFilter() + "' AS START_DATE_FILTER ,'" + arob.getEndDateFilter() + "' AS END_DATE_FILTER, ");
					sb.append(" PV.NHSO_ZONE,PV.PROVINCE_ID,PV.PROVINCE_NAME,ACCALL.BUDGET_YEAR,SUBSTR(CAST(ACCALL.ALLOCATE_CODE AS VARCHAR(8)),1,4) AS ALLOCATE_CODE,  ");
					
					
					if (iBudgetYear <= 2556) {
						
						// sb.append(" SUM(CASE WHEN ACTRN.TRANSACTION_TYPE IN  ('B','S','R','O','F')  AND ACTRN.REASON_CODE = '00' AND ACTRN.ALLOCATE_CODE = ACCALL.ALLOCATE_CODE  ");
						sb.append(" SUM(CASE WHEN ACTRN.TRANSACTION_TYPE IN  ('B')  AND ACTRN.REASON_CODE = '00' AND ACTRN.ALLOCATE_CODE = ACCALL.ALLOCATE_CODE  ");
						sb.append(" AND ACTRN.PROVINCE_ID = PV.PROVINCE_ID AND ACTRN.BUDGET_YEAR = ACCALL.BUDGET_YEAR   ");
						// sb.append(" AND TO_CHAR(INCOME_DATE,'YYYYMMDD') >= '20121001'  AND TO_CHAR(INCOME_DATE,'YYYYMMDD') <= '20130930' ");
						sb.append( condition );
						sb.append( " THEN ACTRN.INCOME_AMOUNT ELSE 0 END )  AS SUM_INCOME_AMOUNT_TYPE_B ,  ");
						
						
					} else{ 
						
					    // SUM_INCOME_AMOUNT_TYPE_B
						sb.append(" ( ");
						sb.append(" SUM(CASE WHEN ACTRN.TRANSACTION_TYPE IN  ('B','S','R','O','F')  AND ACTRN.REASON_CODE = '00' AND ACTRN.ALLOCATE_CODE = ACCALL.ALLOCATE_CODE  ");
						sb.append(" AND ACTRN.PROVINCE_ID = PV.PROVINCE_ID AND ACTRN.BUDGET_YEAR = ACCALL.BUDGET_YEAR   ");
					  //   sb.append(" AND TO_CHAR(INCOME_DATE,'YYYYMMDD') >= '20121001'  AND TO_CHAR(INCOME_DATE,'YYYYMMDD') <= '20130930' ");
						sb.append( condition2 );
						
						sb.append( " THEN ACTRN.INCOME_AMOUNT ELSE 0 END ) ");
						sb.append("  -  ");  
						sb.append(" ( SUM(CASE WHEN ACTRN.TRANSACTION_TYPE IN ('T' , 'P' ) AND ACTRN.REASON_CODE = '00' AND ACTRN.ALLOCATE_CODE = ACCALL.ALLOCATE_CODE  ");
						sb.append(" AND ACTRN.PROVINCE_ID = PV.PROVINCE_ID AND ACTRN.BUDGET_YEAR = ACCALL.BUDGET_YEAR   ");
						// sb.append(" AND TO_CHAR(EXPENSE_DATE,'YYYYMMDD') >= '20121001'  AND TO_CHAR(EXPENSE_DATE,'YYYYMMDD') <= '20130930'  ");
						sb.append( condition2.replaceAll("INCOME", "EXPENSE"));
						sb.append(" THEN ACTRN.EXPENSE_AMOUNT ELSE 0 END )  ");
						sb.append(" + ");   
						sb.append(" SUM(CASE WHEN ACTRN.TRANSACTION_TYPE IN ('C' ) AND ACTRN.REASON_CODE = '00' AND ACTRN.ALLOCATE_CODE = ACCALL.ALLOCATE_CODE ");
						sb.append(" AND ACTRN.PROVINCE_ID = PV.PROVINCE_ID AND ACTRN.BUDGET_YEAR = ACCALL.BUDGET_YEAR   ");
						//sb.append(" AND TO_CHAR(CHEQUE_DATE,'YYYYMMDD') >= '20121001'  AND TO_CHAR(CHEQUE_DATE,'YYYYMMDD') <= '20130930' ");
						sb.append(condition2.replaceAll("INCOME", "CHEQUE"));
						sb.append(" THEN ACTRN.EXPENSE_AMOUNT ELSE 0 END ) ) ");
						sb.append(" ) AS SUM_INCOME_AMOUNT_TYPE_B ,  "); // end TYPE B
					}
					
							  
							// SUM_INCOME_AMOUNT_TYPE_S
							sb.append(" SUM(CASE WHEN ACTRN.TRANSACTION_TYPE = 'S' AND ACTRN.REASON_CODE = '00' AND ACTRN.ALLOCATE_CODE = ACCALL.ALLOCATE_CODE  ");
							sb.append(" AND ACTRN.PROVINCE_ID = PV.PROVINCE_ID AND ACTRN.BUDGET_YEAR = ACCALL.BUDGET_YEAR  ");
							// sb.append(" AND TO_CHAR(INCOME_DATE,'YYYYMMDD') >= '20131001'	  AND TO_CHAR(INCOME_DATE,'YYYYMMDD') <= '20140531' ");
							sb.append(condition);
							sb.append(" THEN ACTRN.INCOME_AMOUNT ELSE 0 END )   AS SUM_INCOME_AMOUNT_TYPE_S ,  "); 
									  
							// SUM_INCOME_AMOUNT
							sb.append(" SUM(CASE WHEN ACTRN.TRANSACTION_TYPE IN ('R','O','F') AND ACTRN.REASON_CODE = '00' AND ACTRN.ALLOCATE_CODE = ACCALL.ALLOCATE_CODE  ");
							sb.append("  AND ACTRN.PROVINCE_ID = PV.PROVINCE_ID AND ACTRN.BUDGET_YEAR = ACCALL.BUDGET_YEAR  ");
							// sb.append(" AND TO_CHAR(INCOME_DATE,'YYYYMMDD') >= '20131001'  AND TO_CHAR(INCOME_DATE,'YYYYMMDD') <= '20140531'   ");
							sb.append(condition);
							sb.append(" THEN ACTRN.INCOME_AMOUNT ELSE 0 END )   AS SUM_INCOME_AMOUNT ,  ");
									  
							// SUM_INCOME_AMOUNT_TYPE_C
							sb.append(" SUM(CASE WHEN ACTRN.TRANSACTION_TYPE IN ('C') AND ACTRN.REASON_CODE = '00' AND ACTRN.ALLOCATE_CODE = ACCALL.ALLOCATE_CODE ");
							sb.append(" AND ACTRN.PROVINCE_ID = PV.PROVINCE_ID AND ACTRN.BUDGET_YEAR = ACCALL.BUDGET_YEAR   ");
							// sb.append(" AND TO_CHAR(CHEQUE_DATE,'YYYYMMDD') >= '20131001'  AND TO_CHAR(CHEQUE_DATE,'YYYYMMDD') <= '20140531' ");
							sb.append(condition.replaceAll("INCOME" , "CHEQUE"));
							sb.append(" THEN ACTRN.EXPENSE_AMOUNT ELSE 0 END )  AS SUM_INCOME_AMOUNT_TYPE_C , ");
								
							// SUM_INCOME_AMOUNT_TYPE_T
							sb.append(" SUM(CASE WHEN ACTRN.TRANSACTION_TYPE IN ('T') AND ACTRN.REASON_CODE = '00' AND ACTRN.ALLOCATE_CODE = ACCALL.ALLOCATE_CODE  ");
							sb.append(" AND ACTRN.PROVINCE_ID = PV.PROVINCE_ID AND ACTRN.BUDGET_YEAR = ACCALL.BUDGET_YEAR   ");
							// sb.append(" AND TO_CHAR(EXPENSE_DATE,'YYYYMMDD') >= '20131001' AND TO_CHAR(EXPENSE_DATE,'YYYYMMDD') <= '20140531'  ");
							sb.append(condition.replaceAll("INCOME", "EXPENSE"));
							sb.append(" THEN ACTRN.EXPENSE_AMOUNT ELSE 0 END ) AS SUM_INCOME_AMOUNT_TYPE_T , ");
									  
							// SUM_INCOME_AMOUNT_TYPE_P
							sb.append(" SUM(CASE WHEN ACTRN.TRANSACTION_TYPE IN ('P') AND ACTRN.REASON_CODE = '00' AND ACTRN.ALLOCATE_CODE = ACCALL.ALLOCATE_CODE ");
							sb.append(" AND ACTRN.PROVINCE_ID = PV.PROVINCE_ID AND ACTRN.BUDGET_YEAR = ACCALL.BUDGET_YEAR   ");
							// sb.append(" AND TO_CHAR(EXPENSE_DATE,'YYYYMMDD') >= '20131001' AND TO_CHAR(EXPENSE_DATE,'YYYYMMDD') <= '20140531'  ");
							sb.append(condition.replaceAll("INCOME", "EXPENSE"));
							sb.append(" THEN ACTRN.EXPENSE_AMOUNT ELSE 0 END ) AS SUM_INCOME_AMOUNT_TYPE_P  ");
									  
							sb.append(" FROM " + this.schema + ".ACC6_ALLOCATE ACCALL ");
							sb.append(" INNER JOIN " + this.schema + ".ACC6_TRANSACTION ACTRN ON ACCALL.ALLOCATE_CODE = ACTRN.ALLOCATE_CODE AND ACCALL.BUDGET_YEAR  =  ACTRN.BUDGET_YEAR ");
							sb.append(" INNER JOIN " + this.schema + ".PROVINCE PV ON ACTRN.PROVINCE_ID = PV.PROVINCE_ID  ");
							sb.append(" WHERE  (LENGTH(ACCALL.ALLOCATE_CODE)) = 8 AND ACCALL.ALLOCATE_LEVEL = CASE WHEN ACCALL.BUDGET_YEAR > 2555 THEN 3 ELSE 2 END  ");
							sb.append(" AND (PV.NHSO_ZONE != 14 AND PV.NHSO_ZONE != 15) OR ( SUBSTR(ACCALL.ALLOCATE_CODE, 3, LENGTH(ACCALL.ALLOCATE_CODE)) = '777777'  ");
							sb.append(" OR SUBSTR(ACCALL.ALLOCATE_CODE, 3, LENGTH(ACCALL.ALLOCATE_CODE)) = '888888' OR SUBSTR(ACCALL.ALLOCATE_CODE, 3,  ");
							sb.append(" LENGTH(ACCALL.ALLOCATE_CODE)) = '999999') ");  
							
							
							sb.append(" GROUP BY  PV.NHSO_ZONE , PV.PROVINCE_ID , PV.PROVINCE_NAME , ACCALL.BUDGET_YEAR  ,CAST(ACCALL.ALLOCATE_CODE AS VARCHAR(8)) ");
							sb.append(" ) ");    // End Query detail 
								
							sb.append(" WHERE 1 > 0 ");
							

						 	//Condition Athor
//							if (!budgetYearFilter.equals("all")) {
//								sb.append(" AND BUDGET_YEAR <= '" + budgetYear + "'");
//							}
							
							if (!provinceIdFilter.equals("all") || ui.getRoleId().equals("3")){	
								sb.append(" AND PROVINCE_ID = '" + provinceIdFilter + "' ");
							} else if (ui.getRoleId().equals("2") && provinceIdFilter.equals("all")) {
								sb.append(" AND NHSO_ZONE = '" + ui.getAreaCode() + "' ");
							}
							
							sb.append(" GROUP BY USER_ID , PROVINCE_ID_FILTER  , BUDGET_YEAR_FILTER  , START_DATE_FILTER , END_DATE_FILTER  , PROVINCE_ID , PROVINCE_NAME ,  SUBSTR(ALLOCATE_CODE , 1 , 4) ");
							sb.append(" ) TRN_DETAIL "); 
							sb.append(" ON  SUBSTR(ACC6_ALLOCATE.ALLOCATE_CODE , 1 , 4)  = TRN_DETAIL.ALLOCATE_CODE AND ACC6_ALLOCATE.ALLOCATE_LEVEL = 1 ");
					 
					  sb.append(" ) "); 
					  
						 //Condition Athor
					//	if (!budgetYearFilter.equals("all")) {
						//	sb.append(" WHERE  BUDGET_YEAR_FILTER <= '" + budgetYear + "'");
					//	}
						
					  sb.append(" GROUP BY USER_ID , PROVINCE_ID_FILTER  , BUDGET_YEAR_FILTER  , START_DATE_FILTER , END_DATE_FILTER , PROVINCE_ID , PROVINCE_NAME ,  ALLOCATE_CODE  ,  ALLOCATE_NAME ");
					  sb.append(" ORDER BY PROVINCE_ID ,  ALLOCATE_CODE ");
					  
					 System.out.println("Report 01 >> " + sb.toString() );
					
					 dao.execDataSql(sb.toString());
				
			this.url = "http://"+this.reportHost+"/BOE/CrystalReports/viewrpt.cwr?&id="+this.idReport3+"&sRefresh=Y" +
		    "&apsuser=" + this.reportUser + "&apspassword=" + this.reportPassword + "" +
		    "&apsauthtype=" + this.reportType + "&promptex-userId=" + ui.getUserId();
			response.sendRedirect(this.url);
					
			request.setAttribute("budgetYearFilter", budgetYearFilter);
			request.setAttribute("provinceIdFilter", provinceIdFilter);
			request.setAttribute("lsBudgetYear", this.dataUtil(dao).lsBudgetYear());
			conn.commit();
			
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			e.printStackTrace();
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
				this.url = "";
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}

	/**
	 * @modify CEO FreeALife  Date 18/04/2014
	 * @param request
	 * @param response
	 * @return
	 */
	private String ACC6REPORT002 (HttpServletRequest request, HttpServletResponse response) {
		
		System.out.println("---- Process name ACC6REPORT002 -----");
		
		Connection conn = null;
		ISUD dao = null;
		
		try {
			
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			UserInfo ui = this.user(request);
			Acc6ReportFundsStatusBean afsb = new Acc6ReportFundsStatusBean();
			
			String provinceIdFilter = this.getAttr(request, "provinceIdFilter");
			String budgetYearFilter = this.getAttr(request, "budgetYearFilter"); 
			String startDate = this.getAttr(request, "startDateFilter");
			String endDate = this.getAttr(request, "endDateFilter");
			String allocYearFilter = this.getAttr(request, "allocYearFilter");
			String condition = "";
			String conditionIncomeAmtB = "";
			int iBudgetYearFilter = Integer.parseInt(budgetYearFilter);
			
			int iAllocYearFilter = 0;
			StringBuffer sb = new StringBuffer();
			
			if (provinceIdFilter.equals("")) {
				provinceIdFilter = "all";
			}
			
			if (budgetYearFilter.equals("")) {
				budgetYearFilter = "all";
			}
			
			if (allocYearFilter.equals("")) {
				allocYearFilter = "all";
			} else {
				iAllocYearFilter = Integer.parseInt(allocYearFilter);
			}
			
			afsb.setProvinceIdFilter(provinceIdFilter);
			afsb.setBudgetYearFilter(budgetYearFilter);
			afsb.setStartDateFilter(this.dateUtil().changeFormatDate2YYYYMMDD(startDate, "/", ""));
			afsb.setEndDateFilter(this.dateUtil().changeFormatDate2YYYYMMDD(endDate, "/", ""));
			
			sb.append("DELETE FROM "+this.schema+".ACC6_REPORT_FUNDS_STATUS WHERE USER_ID = '" + ui.getUserId() + "' ");
			
			System.out.println(" SQL Process  : " + sb.toString());
			System.out.println("start date  : " + startDate );
			System.out.println("end date : " +endDate);
			
			dao.execDataSql(sb.toString());
			
			condition = "";
			
			if (ConfigBundle.getValue("DEPLOY_TYPE").equals("P")) {
				startDate = this.dateUtil().changeDateToDB(startDate);
				endDate = this.dateUtil().changeDateToDB(endDate);
				condition = " AND TO_DATE(INCOME_DATE,'DD.MM.YYYY') >= " + startDate + " AND TO_DATE(INCOME_DATE,'DD.MM.YYYY') <= " + endDate + " ";
				
				// Old SQL 
				conditionIncomeAmtB = " AND TO_DATE(INCOME_DATE,'DD.MM.YYYY') >= TO_DATE('01/10/2012','DD.MM.YYYY') AND TO_DATE(INCOME_DATE,'DD.MM.YYYY') <= TO_DATE('30/09/2013','DD.MM.YYYY') ";
			
			} else {
			
				startDate =  this.dateUtil().changeFormatDate2YYYYMMDD(startDate, "/", "");
				endDate = this.dateUtil().changeFormatDate2YYYYMMDD(endDate, "/", "");
				condition = " AND TO_CHAR(INCOME_DATE,'YYYYMMDD') >= '" + startDate + "' AND TO_CHAR(INCOME_DATE,'YYYYMMDD') <= '" + endDate + "' ";
				conditionIncomeAmtB = " AND TO_CHAR(INCOME_DATE,'YYYYMMDD') >= '20121001' AND TO_CHAR(INCOME_DATE,'YYYYMMDD') <= '20130930' ";
			
			}
				
			// Reset value
			sb.setLength(0);
			
			//INCOME
			sb.append("INSERT INTO " + this.schema + ".ACC6_REPORT_FUNDS_STATUS ( USER_ID,PROVINCE_ID_FILTER,BUDGET_YEAR_FILTER,START_DATE_FILTER,END_DATE_FILTER");
			sb.append(",NHSO_ZONE,PROVINCE_ID,PROVINCE_NAME,BUDGET_YEAR,ALLOCATE_CODE,ALLOCATE_NAME,SUM_INCOME_AMOUNT_TYPE_B,SUM_INCOME_AMOUNT_TYPE_S,SUM_INCOME_AMOUNT ");
			sb.append(",SUM_EXPENSE_AMOUNT_TYPE_C,SUM_EXPENSE_AMOUNT_TYPE_T,SUM_EXPENSE_AMOUNT_TYPE_P) ");
			
			 // select order by ACC6_REPORT_FUNDS_STATUS
			sb.append("SELECT * FROM  ( SELECT '" + ui.getUserId() + "' AS USER_ID,'" + afsb.getProvinceIdFilter() + "' AS PROVINCE_ID_FILTER,'" + afsb.getBudgetYearFilter() + "' AS BUDGET_YEAR_FILTER,'" + afsb.getStartDateFilter() + "' AS START_DATE_FILTER "); 
			sb.append(",'"  + afsb.getEndDateFilter() +  "'AS END_DATE_FILTER,PV.NHSO_ZONE,PV.PROVINCE_ID,PV.PROVINCE_NAME,ACCALL.BUDGET_YEAR,CAST(ACCALL.ALLOCATE_CODE AS VARCHAR(8)) AS ALLOCATE_CODE, ACCALL.ALLOCATE_NAME , ");
			
			// Check condition Type B 
			if (allocYearFilter.equals("all")) {
				
				if (iBudgetYearFilter <= 2556) {
				
					sb.append(" SUM(CASE WHEN ACTRN.TRANSACTION_TYPE = 'B' AND ACTRN.REASON_CODE = '00' AND ACTRN.ALLOCATE_CODE = ACCALL.ALLOCATE_CODE ");
					sb.append(" AND ACTRN.PROVINCE_ID = PV.PROVINCE_ID AND ACTRN.BUDGET_YEAR = ACCALL.BUDGET_YEAR  ");
					sb.append( condition + " THEN ACTRN.INCOME_AMOUNT ELSE 0 END )  ");
					sb.append(" AS SUM_INCOME_AMOUNT_TYPE_B , ");
					
				} else {
					
					System.out.println("[ INFO BY PROGRAMMER  ]  : SQL statement select field value  ]");
					
					// ACTIOIN
					sb.append(" ( SUM(CASE WHEN ACTRN.TRANSACTION_TYPE IN  ('B','S','R','O','F')  AND ACTRN.REASON_CODE = '00' AND ACTRN.ALLOCATE_CODE = ACCALL.ALLOCATE_CODE ");
					sb.append(" AND ACTRN.PROVINCE_ID = PV.PROVINCE_ID AND ACTRN.BUDGET_YEAR = ACCALL.BUDGET_YEAR  ");
					sb.append( conditionIncomeAmtB + " THEN ACTRN.INCOME_AMOUNT ELSE 0 END )  ");
					sb.append(" - ");
					sb.append(" ( SUM(CASE WHEN ACTRN.TRANSACTION_TYPE IN ('T' , 'P' ) AND ACTRN.REASON_CODE = '00' AND ACTRN.ALLOCATE_CODE = ACCALL.ALLOCATE_CODE ");
					sb.append("AND ACTRN.PROVINCE_ID = PV.PROVINCE_ID AND ACTRN.BUDGET_YEAR = ACCALL.BUDGET_YEAR  ");
					sb.append( conditionIncomeAmtB.replaceAll("INCOME_DATE", "EXPENSE_DATE") + " THEN ACTRN.EXPENSE_AMOUNT ELSE 0 END ) ");
					sb.append(" +  ");
					sb.append(" SUM(CASE WHEN ACTRN.TRANSACTION_TYPE IN ('C' ) AND ACTRN.REASON_CODE = '00' AND ACTRN.ALLOCATE_CODE = ACCALL.ALLOCATE_CODE ");
					sb.append("AND ACTRN.PROVINCE_ID = PV.PROVINCE_ID AND ACTRN.BUDGET_YEAR = ACCALL.BUDGET_YEAR  ");
					sb.append( conditionIncomeAmtB.replaceAll("INCOME_DATE", "CHEQUE_DATE")  + " THEN ACTRN.EXPENSE_AMOUNT ELSE 0 END ) ) )");
					
					sb.append(" AS SUM_INCOME_AMOUNT_TYPE_B , ");
					
				}
			
			} else {
				
				// if choose allocate year.
				if (iAllocYearFilter <= 2556) {
					
					if (iBudgetYearFilter <= 2556) {
				
						sb.append(" SUM(CASE WHEN ACTRN.TRANSACTION_TYPE = 'B' AND ACTRN.REASON_CODE = '00' AND ACTRN.ALLOCATE_CODE = ACCALL.ALLOCATE_CODE ");
						sb.append(" AND ACTRN.PROVINCE_ID = PV.PROVINCE_ID AND ACTRN.BUDGET_YEAR = ACCALL.BUDGET_YEAR  ");
						sb.append( condition + " THEN ACTRN.INCOME_AMOUNT ELSE 0 END )  ");
						sb.append(" AS SUM_INCOME_AMOUNT_TYPE_B , ");
						
					} else {
					
						// ACTIOIN
						sb.append(" ( SUM(CASE WHEN ACTRN.TRANSACTION_TYPE IN  ('B','S','R','O','F')  AND ACTRN.REASON_CODE = '00' AND ACTRN.ALLOCATE_CODE = ACCALL.ALLOCATE_CODE ");
						sb.append(" AND ACTRN.PROVINCE_ID = PV.PROVINCE_ID AND ACTRN.BUDGET_YEAR = ACCALL.BUDGET_YEAR  ");
						sb.append( conditionIncomeAmtB + " THEN ACTRN.INCOME_AMOUNT ELSE 0 END )  ");
						sb.append(" - ");
						sb.append(" ( SUM(CASE WHEN ACTRN.TRANSACTION_TYPE IN ('T' , 'P' ) AND ACTRN.REASON_CODE = '00' AND ACTRN.ALLOCATE_CODE = ACCALL.ALLOCATE_CODE ");
						sb.append("AND ACTRN.PROVINCE_ID = PV.PROVINCE_ID AND ACTRN.BUDGET_YEAR = ACCALL.BUDGET_YEAR  ");
						sb.append( conditionIncomeAmtB.replaceAll("INCOME_DATE", "EXPENSE_DATE") + " THEN ACTRN.EXPENSE_AMOUNT ELSE 0 END ) ");
						sb.append(" +  ");
						sb.append(" SUM(CASE WHEN ACTRN.TRANSACTION_TYPE IN ('C' ) AND ACTRN.REASON_CODE = '00' AND ACTRN.ALLOCATE_CODE = ACCALL.ALLOCATE_CODE ");
						sb.append("AND ACTRN.PROVINCE_ID = PV.PROVINCE_ID AND ACTRN.BUDGET_YEAR = ACCALL.BUDGET_YEAR  ");
						sb.append( conditionIncomeAmtB.replaceAll("INCOME_DATE", "CHEQUE_DATE")  + " THEN ACTRN.EXPENSE_AMOUNT ELSE 0 END ) ) )");
						
						sb.append(" AS SUM_INCOME_AMOUNT_TYPE_B , ");
				
					}
					
				} else {
					
					// ACTIOIN
					sb.append(" ( SUM(CASE WHEN ACTRN.TRANSACTION_TYPE IN  ('B','S','R','O','F')  AND ACTRN.REASON_CODE = '00' AND ACTRN.ALLOCATE_CODE = ACCALL.ALLOCATE_CODE ");
					sb.append(" AND ACTRN.PROVINCE_ID = PV.PROVINCE_ID AND ACTRN.BUDGET_YEAR = ACCALL.BUDGET_YEAR  ");
					sb.append( conditionIncomeAmtB + " THEN ACTRN.INCOME_AMOUNT ELSE 0 END )  ");
					sb.append(" - ");
					sb.append(" ( SUM(CASE WHEN ACTRN.TRANSACTION_TYPE IN ('T' , 'P' ) AND ACTRN.REASON_CODE = '00' AND ACTRN.ALLOCATE_CODE = ACCALL.ALLOCATE_CODE ");
					sb.append("AND ACTRN.PROVINCE_ID = PV.PROVINCE_ID AND ACTRN.BUDGET_YEAR = ACCALL.BUDGET_YEAR  ");
					sb.append( conditionIncomeAmtB.replaceAll("INCOME_DATE", "EXPENSE_DATE") + " THEN ACTRN.EXPENSE_AMOUNT ELSE 0 END ) ");
					sb.append(" +  ");
					sb.append(" SUM(CASE WHEN ACTRN.TRANSACTION_TYPE IN ('C' ) AND ACTRN.REASON_CODE = '00' AND ACTRN.ALLOCATE_CODE = ACCALL.ALLOCATE_CODE ");
					sb.append("AND ACTRN.PROVINCE_ID = PV.PROVINCE_ID AND ACTRN.BUDGET_YEAR = ACCALL.BUDGET_YEAR  ");
					sb.append( conditionIncomeAmtB.replaceAll("INCOME_DATE", "CHEQUE_DATE")  + " THEN ACTRN.EXPENSE_AMOUNT ELSE 0 END ) ) )");
					
					sb.append(" AS SUM_INCOME_AMOUNT_TYPE_B , ");
					
					 // sb.append(" 0.00 AS SUM_INCOME_AMOUNT_TYPE_B , ");
					
				}
				
			} //  end Type B 
		
			// Income S 
			sb.append(" SUM(CASE WHEN ACTRN.TRANSACTION_TYPE = 'S' AND ACTRN.REASON_CODE = '00' AND ACTRN.ALLOCATE_CODE = ACCALL.ALLOCATE_CODE ");
			sb.append(" AND ACTRN.PROVINCE_ID = PV.PROVINCE_ID AND ACTRN.BUDGET_YEAR = ACCALL.BUDGET_YEAR  ");
			sb.append(  condition + "THEN ACTRN.INCOME_AMOUNT ELSE 0 END )  ");
			sb.append(" AS SUM_INCOME_AMOUNT_TYPE_S , ");
		
			// Income  R , O , F
			sb.append(" SUM(CASE WHEN ACTRN.TRANSACTION_TYPE IN ('R','O','F') AND ACTRN.REASON_CODE = '00' AND ACTRN.ALLOCATE_CODE = ACCALL.ALLOCATE_CODE  ");
			sb.append(" AND ACTRN.PROVINCE_ID = PV.PROVINCE_ID AND ACTRN.BUDGET_YEAR = ACCALL.BUDGET_YEAR  ");
			sb.append(  condition + "  THEN ACTRN.INCOME_AMOUNT ELSE 0 END )  ");
			sb.append(" AS SUM_INCOME_AMOUNT ,  ");
				  
			//EXPENSE  Date Modify
			condition = condition.replaceAll("INCOME", "EXPENSE");

			// Expense type C
			sb.append("SUM(CASE WHEN ACTRN.TRANSACTION_TYPE IN ('C') AND ACTRN.REASON_CODE = '00' AND ACTRN.ALLOCATE_CODE = ACCALL.ALLOCATE_CODE ");
			sb.append("AND ACTRN.PROVINCE_ID = PV.PROVINCE_ID AND ACTRN.BUDGET_YEAR = ACCALL.BUDGET_YEAR  ");
			sb.append(condition.replaceAll("EXPENSE", "CHEQUE") + " THEN ACTRN.EXPENSE_AMOUNT ELSE 0 END ) ");
			sb.append(" AS SUM_INCOME_AMOUNT_TYPE_C ,");
		
			// Expense type T
			sb.append("SUM(CASE WHEN ACTRN.TRANSACTION_TYPE IN ('T') AND ACTRN.REASON_CODE = '00' AND ACTRN.ALLOCATE_CODE = ACCALL.ALLOCATE_CODE ");
			sb.append("AND ACTRN.PROVINCE_ID = PV.PROVINCE_ID AND ACTRN.BUDGET_YEAR = ACCALL.BUDGET_YEAR  ");
			sb.append( condition + " THEN ACTRN.EXPENSE_AMOUNT ELSE 0 END ) ");
			sb.append("AS SUM_INCOME_AMOUNT_TYPE_T ,");
		
			//  Expense Type P
			sb.append("SUM(CASE WHEN ACTRN.TRANSACTION_TYPE IN ('P') AND ACTRN.REASON_CODE = '00' AND ACTRN.ALLOCATE_CODE = ACCALL.ALLOCATE_CODE ");
			sb.append("AND ACTRN.PROVINCE_ID = PV.PROVINCE_ID AND ACTRN.BUDGET_YEAR = ACCALL.BUDGET_YEAR  ");
			sb.append(condition + " THEN ACTRN.EXPENSE_AMOUNT ELSE 0 END )"); 
			sb.append("AS SUM_INCOME_AMOUNT_TYPE_P  ");
			
			// Form Table 
			sb.append(" FROM " + this.schema + ".ACC6_ALLOCATE ACCALL ");
			sb.append(" INNER JOIN " + this.schema + ".ACC6_TRANSACTION ACTRN  ON ACCALL.ALLOCATE_CODE = ACTRN.ALLOCATE_CODE AND ACCALL.BUDGET_YEAR  =  ACTRN.BUDGET_YEAR");
			sb.append(" INNER JOIN " + this.schema + ".PROVINCE PV ON ACTRN.PROVINCE_ID = PV.PROVINCE_ID ");

			// Where condition
			sb.append(" WHERE ");
			sb.append(" (LENGTH(ACCALL.ALLOCATE_CODE)) = 8 AND ACCALL.ALLOCATE_LEVEL = CASE WHEN ACCALL.BUDGET_YEAR > 2555 THEN 3 ELSE 2 END ");
			sb.append(" AND (PV.NHSO_ZONE != 14 AND PV.NHSO_ZONE != 15) OR ( SUBSTR(ACCALL.ALLOCATE_CODE, 3, LENGTH(ACCALL.ALLOCATE_CODE)) = '777777' ");
			sb.append(" OR SUBSTR(ACCALL.ALLOCATE_CODE, 3, LENGTH(ACCALL.ALLOCATE_CODE)) = '888888' OR SUBSTR(ACCALL.ALLOCATE_CODE, 3, LENGTH(ACCALL.ALLOCATE_CODE)) = '999999') ");
			
			// Condition Other
			
			if (!allocYearFilter.equals("all")) {
				sb.append(" AND ACCALL.BUDGET_YEAR = '" + allocYearFilter + "'");
			}
			
			if (!provinceIdFilter.equals("all") || ui.getRoleId().equals("3")){	
				sb.append(" AND PV.PROVINCE_ID = '" + provinceIdFilter + "' ");
			} else if (ui.getRoleId().equals("2") && provinceIdFilter.equals("all")) {
				sb.append(" AND PV.NHSO_ZONE = '" + ui.getAreaCode() + "' ");
			}
			
			// Group by 
			sb.append(" GROUP BY "); 
			sb.append(" PV.NHSO_ZONE , PV.PROVINCE_ID , PV.PROVINCE_NAME , ACCALL.BUDGET_YEAR  ,CAST(ACCALL.ALLOCATE_CODE AS VARCHAR(8)), ACCALL.ALLOCATE_NAME ");

			// Having
			sb.append(" HAVING "); 
		
			// sb.append(" ( "); 
			
			//  Case No have data sql will check value test data.
			// ----------------------------------------------------- if have condition by ACC6_TRANSACTION_TYPE  = 'B'
		    // Check condition Type B 
						if (allocYearFilter.equals("all")) {
							if (iBudgetYearFilter <= 2556) {
								sb.append(" SUM(CASE WHEN ACTRN.TRANSACTION_TYPE = 'B' AND ACTRN.REASON_CODE = '00' AND ACTRN.ALLOCATE_CODE = ACCALL.ALLOCATE_CODE ");
								sb.append(" AND ACTRN.PROVINCE_ID = PV.PROVINCE_ID AND ACTRN.BUDGET_YEAR = ACCALL.BUDGET_YEAR  ");
								sb.append( condition.replaceAll("EXPENSE", "INCOME") + " THEN ACTRN.INCOME_AMOUNT ELSE 0 END )  != 0 ");
							} else {
								
								System.out.println("[ INFO BY PROGRAMMER  ]  : SQL statement having  ]");
								
								// ACTIOIN
								sb.append(" ( SUM(CASE WHEN ACTRN.TRANSACTION_TYPE IN  ('B','S','R','O','F')  AND ACTRN.REASON_CODE = '00' AND ACTRN.ALLOCATE_CODE = ACCALL.ALLOCATE_CODE ");
								sb.append(" AND ACTRN.PROVINCE_ID = PV.PROVINCE_ID AND ACTRN.BUDGET_YEAR = ACCALL.BUDGET_YEAR  ");
								sb.append( conditionIncomeAmtB + " THEN ACTRN.INCOME_AMOUNT ELSE 0 END )  ");
								sb.append(" - ");
								sb.append(" ( SUM(CASE WHEN ACTRN.TRANSACTION_TYPE IN ('T' , 'P' ) AND ACTRN.REASON_CODE = '00' AND ACTRN.ALLOCATE_CODE = ACCALL.ALLOCATE_CODE ");
								sb.append("AND ACTRN.PROVINCE_ID = PV.PROVINCE_ID AND ACTRN.BUDGET_YEAR = ACCALL.BUDGET_YEAR  ");
								sb.append( conditionIncomeAmtB.replaceAll("INCOME_DATE", "EXPENSE_DATE") + " THEN ACTRN.EXPENSE_AMOUNT ELSE 0 END ) ");
								sb.append(" +  ");
								sb.append(" SUM(CASE WHEN ACTRN.TRANSACTION_TYPE IN ('C' ) AND ACTRN.REASON_CODE = '00' AND ACTRN.ALLOCATE_CODE = ACCALL.ALLOCATE_CODE ");
								sb.append("AND ACTRN.PROVINCE_ID = PV.PROVINCE_ID AND ACTRN.BUDGET_YEAR = ACCALL.BUDGET_YEAR  ");
								sb.append( conditionIncomeAmtB.replaceAll("INCOME_DATE", "CHEQUE_DATE")  + " THEN ACTRN.EXPENSE_AMOUNT ELSE 0 END ) ) )  != 0 ");
							}
						} else {
							// if choose allocate year.
							if (iAllocYearFilter <= 2556) {
								if (iBudgetYearFilter <= 2556) {
									sb.append(" SUM(CASE WHEN ACTRN.TRANSACTION_TYPE = 'B' AND ACTRN.REASON_CODE = '00' AND ACTRN.ALLOCATE_CODE = ACCALL.ALLOCATE_CODE ");
									sb.append(" AND ACTRN.PROVINCE_ID = PV.PROVINCE_ID AND ACTRN.BUDGET_YEAR = ACCALL.BUDGET_YEAR  ");
									sb.append( condition.replaceAll("EXPENSE", "INCOME") + " THEN ACTRN.INCOME_AMOUNT ELSE 0 END )  != 0 ");
								} else {
									// ACTIOIN
									sb.append(" ( SUM(CASE WHEN ACTRN.TRANSACTION_TYPE IN  ('B','S','R','O','F')  AND ACTRN.REASON_CODE = '00' AND ACTRN.ALLOCATE_CODE = ACCALL.ALLOCATE_CODE ");
									sb.append(" AND ACTRN.PROVINCE_ID = PV.PROVINCE_ID AND ACTRN.BUDGET_YEAR = ACCALL.BUDGET_YEAR  ");
									sb.append( conditionIncomeAmtB + " THEN ACTRN.INCOME_AMOUNT ELSE 0 END )  ");
									sb.append(" - ");
									sb.append(" ( SUM(CASE WHEN ACTRN.TRANSACTION_TYPE IN ('T' , 'P' ) AND ACTRN.REASON_CODE = '00' AND ACTRN.ALLOCATE_CODE = ACCALL.ALLOCATE_CODE ");
									sb.append("AND ACTRN.PROVINCE_ID = PV.PROVINCE_ID AND ACTRN.BUDGET_YEAR = ACCALL.BUDGET_YEAR  ");
									sb.append( conditionIncomeAmtB.replaceAll("INCOME_DATE", "EXPENSE_DATE") + " THEN ACTRN.EXPENSE_AMOUNT ELSE 0 END ) ");
									sb.append(" +  ");
									sb.append(" SUM(CASE WHEN ACTRN.TRANSACTION_TYPE IN ('C' ) AND ACTRN.REASON_CODE = '00' AND ACTRN.ALLOCATE_CODE = ACCALL.ALLOCATE_CODE ");
									sb.append("AND ACTRN.PROVINCE_ID = PV.PROVINCE_ID AND ACTRN.BUDGET_YEAR = ACCALL.BUDGET_YEAR  ");
									sb.append( conditionIncomeAmtB.replaceAll("INCOME_DATE", "CHEQUE_DATE")  + " THEN ACTRN.EXPENSE_AMOUNT ELSE 0 END ) ) )  != 0 ");
								}
							} else {
								  sb.append(" 1 > 0  ");
							}
						} //  end Type B 
			//------------------------------------------------------ end  ACC6_TRANSACTION_TYPE  = 'B' 
			// Type B  != 0 
			//sb.append(" SUM(CASE WHEN ACTRN.TRANSACTION_TYPE = 'B' AND ACTRN.REASON_CODE = '00' AND ACTRN.ALLOCATE_CODE = ACCALL.ALLOCATE_CODE "); 
			//sb.append(" AND ACTRN.PROVINCE_ID = PV.PROVINCE_ID AND ACTRN.BUDGET_YEAR = ACCALL.BUDGET_YEAR  "); 
		   //sb.append(condition.replaceAll("EXPENSE", "INCOME") + " THEN ACTRN.INCOME_AMOUNT ELSE 0 END )  != 0  "); 
			  
			// Type S != 0
			 sb.append(" OR ");
			//sb.append(" + ");
			sb.append(" SUM(CASE WHEN ACTRN.TRANSACTION_TYPE = 'S' AND ACTRN.REASON_CODE = '00' AND ACTRN.ALLOCATE_CODE = ACCALL.ALLOCATE_CODE  "); 
			sb.append(" AND ACTRN.PROVINCE_ID = PV.PROVINCE_ID AND ACTRN.BUDGET_YEAR = ACCALL.BUDGET_YEAR   "); 
			sb.append(condition.replaceAll("EXPENSE", "INCOME") + "  THEN ACTRN.INCOME_AMOUNT ELSE 0 END )   != 0 "); 
			 
			// Type amount All != 0
			 sb.append(" OR "); 
			// sb.append(" + ");
			sb.append(" SUM(CASE WHEN ACTRN.TRANSACTION_TYPE IN ('R','O','F') AND ACTRN.REASON_CODE = '00' AND ACTRN.ALLOCATE_CODE = ACCALL.ALLOCATE_CODE "); 
			sb.append(" AND ACTRN.PROVINCE_ID = PV.PROVINCE_ID AND ACTRN.BUDGET_YEAR = ACCALL.BUDGET_YEAR   "); 
			sb.append( condition.replaceAll("EXPENSE", "INCOME")  + " THEN ACTRN.INCOME_AMOUNT ELSE 0 END )  != 0 "); 
			 
			// Type C != 0
			 sb.append(" OR "); 
			// sb.append(" + ");
			sb.append(" SUM(CASE WHEN ACTRN.TRANSACTION_TYPE IN ('C') AND ACTRN.REASON_CODE = '00' AND ACTRN.ALLOCATE_CODE = ACCALL.ALLOCATE_CODE  "); 
			sb.append(" AND ACTRN.PROVINCE_ID = PV.PROVINCE_ID AND ACTRN.BUDGET_YEAR = ACCALL.BUDGET_YEAR  "); 
			sb.append(condition.replaceAll("EXPENSE", "CHEQUE") + " THEN ACTRN.EXPENSE_AMOUNT ELSE 0 END )  != 0 "); 
			  
			// Type T != 0
			 sb.append("OR "); 
			// sb.append(" + ");
			sb.append(" SUM(CASE WHEN ACTRN.TRANSACTION_TYPE IN ('T') AND ACTRN.REASON_CODE = '00' AND ACTRN.ALLOCATE_CODE = ACCALL.ALLOCATE_CODE  "); 
			sb.append("AND ACTRN.PROVINCE_ID = PV.PROVINCE_ID AND ACTRN.BUDGET_YEAR = ACCALL.BUDGET_YEAR  "); 
			sb.append(condition  + " THEN ACTRN.EXPENSE_AMOUNT ELSE 0 END )  != 0 "); 
			//sb.append("AND TO_CHAR(ACTRN.EXPENSE_DATE,'YYYYMMDD') >= '20121001'  AND TO_CHAR(ACTRN.EXPENSE_DATE,'YYYYMMDD') <= '20140417' THEN ACTRN.EXPENSE_AMOUNT ELSE 0 END )  "); 
			 
			// Type P  != 0
			sb.append("OR "); 
			//sb.append(" + ");
			sb.append("SUM(CASE WHEN ACTRN.TRANSACTION_TYPE IN ('P') AND ACTRN.REASON_CODE = '00' AND ACTRN.ALLOCATE_CODE = ACCALL.ALLOCATE_CODE "); 
			sb.append("AND ACTRN.PROVINCE_ID = PV.PROVINCE_ID AND ACTRN.BUDGET_YEAR = ACCALL.BUDGET_YEAR  "); 
			sb.append(condition  + " THEN ACTRN.EXPENSE_AMOUNT ELSE 0 END )  != 0 ");
			
			//sb.append("AND TO_CHAR(ACTRN.EXPENSE_DATE,'YYYYMMDD') >= '20121001'  AND TO_CHAR(ACTRN.EXPENSE_DATE,'YYYYMMDD') <= '20140417' THEN ACTRN.EXPENSE_AMOUNT ELSE 0 END ) "); 
			//sb.append(" )  != 0"); 
			
			// Order by 
			sb.append(" ORDER BY ACCALL.BUDGET_YEAR DESC ,  ALLOCATE_CODE ASC ");
			sb.append(" ) "); 

			// where outer 
			sb.append(" WHERE 1 > 0  ");

			if (!allocYearFilter.equals("all")) {
				sb.append(" AND BUDGET_YEAR = '" + allocYearFilter + "' ");
			}
			
			if (!provinceIdFilter.equals("all") || ui.getRoleId().equals("3")){	
				sb.append(" AND PROVINCE_ID = '" + provinceIdFilter + "' ");
			} else if (ui.getRoleId().equals("2") && provinceIdFilter.equals("all")) {
				sb.append(" AND NHSO_ZONE = '" + ui.getAreaCode() + "' ");
			}
			
			System.out.println(" SQL Process  REPORT 08 : " + sb.toString());
			
		    dao.execDataSql(sb.toString());
			
			this.url = "http://"+this.reportHost+"/BOE/CrystalReports/viewrpt.cwr?&id="+this.idReport4+"&sRefresh=Y" +
		    "&apsuser=" + this.reportUser + "&apspassword=" + this.reportPassword + "" +
		    "&apsauthtype=" + this.reportType + "&promptex-userId=" + ui.getUserId();
			
			response.sendRedirect(this.url);
			request.setAttribute("budgetYearFilter", budgetYearFilter);
			request.setAttribute("provinceIdFilter", provinceIdFilter);
			request.setAttribute("startDateFilter", this.getAttr(request, "startDateFilter"));
			request.setAttribute("endDateFilter", this.getAttr(request, "endDateFilter"));
			request.setAttribute("lsBudgetYear", this.dataUtil(dao).lsBudgetYear());
			
			conn.commit();
			
		} catch (Exception e) {
			
			try {
				
				if (conn != null) {
					conn.rollback();
				}
				
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			
			e.printStackTrace();
			
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
			
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
				this.url = "";
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	
	}
	
	private String ACC6REPORT003 (HttpServletRequest request, HttpServletResponse response) {
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			UserInfo ui = this.user(request);
			Acc6ReportSummaryBean arsb = new Acc6ReportSummaryBean();
			
			String provinceIdFilter = this.getAttr(request, "provinceIdFilter");
			String budgetYearFilter = this.getAttr(request, "budgetYearFilter"); 
			String allocateCodeFilter = this.getAttr(request, "allocateCodeFilter");
			String allocateCodeLvl2Filter = this.getAttr(request, "allocateCodeLvl2Filter");
			String condition = "";
			//String allocateCode = allocateCodeFilter;
			//String allocateCodeLvl2 = allocateCodeLvl2Filter;
			StringBuffer sb = new StringBuffer();
			
			if (provinceIdFilter.equals("")) {
				provinceIdFilter = "all";
			}
			if (allocateCodeLvl2Filter.equals("")) {
				allocateCodeLvl2Filter = "all";
			}
			arsb.setProvinceIdFilter(provinceIdFilter);
			arsb.setBudgetYearFilter(budgetYearFilter);
			arsb.setAllocateCodeFilter(allocateCodeFilter);
			arsb.setAllocateCodeLvl2Filter(allocateCodeLvl2Filter);

			String idReport = "";
			if (Integer.parseInt(budgetYearFilter) < 2556) {
				idReport = this.idReport22;
			} else {
				idReport = this.idReport21;
			}
			
			sb.append("DELETE FROM "+this.schema+".ACC6_REPORT_SUMMARY WHERE USER_ID = '" + ui.getUserId() + "' ");
			dao.execDataSql(sb.toString());
			if (!provinceIdFilter.equals("all") || ui.getRoleId().equals("3")){	
				condition += " AND T1.PROVINCE_ID = '" + provinceIdFilter + "' ";
			} else if (ui.getRoleId().equals("2") && provinceIdFilter.equals("all")) {
				condition += " AND T1.PROVINCE_ID IN (SELECT PROVINCE_ID FROM "+ this.schema + ".PROVINCE WHERE NHSO_ZONE = '" + ui.getAreaCode() + "') ";
			}
			if (!allocateCodeLvl2Filter.equals("all")) {
				condition += " AND T1.ALLOCATE_CODE LIKE '" + allocateCodeLvl2Filter.substring(0, 6) + "%'";
			} else {
				condition += " AND T1.ALLOCATE_CODE LIKE '" + allocateCodeFilter + "%'";
			}
			
			sb.setLength(0);
			sb.append("INSERT INTO "+ this.schema + ".ACC6_REPORT_SUMMARY (USER_ID,PROVINCE_ID_FILTER,BUDGET_YEAR_FILTER,ALLOCATE_CODE_FILTER,");
			sb.append("ALLOCATE_CODE_LVL_2_FILTER,EXPENSE_DATE,DOC_NO,REF_NO,EXPENSE_DESC,EXPENSE_AMOUNT,INCOME_AMOUNT,ALLOCATE_CODE,ALLOCATE_NAME,");
			sb.append("PROVINCE_ID,ALLOC_PARENT_CODE) ");
			/*sb.append("SELECT '" + ui.getUserId() + "','" + provinceIdFilter + "','" + budgetYearFilter + "','" + allocateCodeFilter + "','" + allocateCodeLvl2Filter + "'");
			sb.append(",'' AS EXPENSE_DATE ,'' AS DOC_NO, '' AS REF_NO ,'' AS EXPENSE_DESC ");
			sb.append(",(SELECT SUM(EXPENSE_AMOUNT) AS EXPENSE_AMOUNT FROM "+ this.schema + ".ACC6_TRANSACTION WHERE REASON_CODE = '00'  AND PROVINCE_ID = '" + provinceIdFilter + "'  AND ALLOCATE_CODE LIKE '" + allocateCodeFilter + "%') AS EXPENSE_AMOUNT ");
			sb.append(",(SELECT SUM(INCOME_AMOUNT) AS INCOME_AMOUNT FROM "+ this.schema + ".ACC6_TRANSACTION WHERE REASON_CODE = '00'  AND PROVINCE_ID = '" + provinceIdFilter + "'  AND ALLOCATE_CODE LIKE '" + allocateCodeFilter + "%') AS INCOME_AMOUNT ");
			sb.append(",0 AS ALLOCATE_CODE,'SUM' AS ALLOCATE_NAME ,PROVINCE_ID, " + allocateCodeFilter + " AS ALLOC_PARENT_CODE ");
			sb.append("FROM "+ this.schema + ".ACC6_TRANSACTION ");
			sb.append("WHERE PROVINCE_ID = '" + provinceIdFilter + "' ");
			sb.append("UNION ");*/
			sb.append("SELECT '" + ui.getUserId() + "','" + provinceIdFilter + "','" + budgetYearFilter + "','" + allocateCodeFilter + "','" + allocateCodeLvl2Filter + "'");
			sb.append(",TO_CHAR((SELECT DISTINCT EXPENSE_DATE FROM "+ this.schema + ".ACC6_EXPENSE WHERE T2.DOC_NO = DOC_NO),'DD/MM/YYYY') AS EXPENSE_DATE ,T2.DOC_NO, T2.REF_NO ");
			sb.append(",(SELECT DISTINCT EXPENSE_DESC FROM "+ this.schema + ".ACC6_EXPENSE WHERE T2.DOC_NO = DOC_NO) AS EXPENSE_DESC, T1.EXPENSE_AMOUNT, 0 AS INCOME_AMOUNT ");
			sb.append(",T1.ALLOCATE_CODE,(SELECT DISTINCT ALLOCATE_NAME FROM "+ this.schema + ".ACC6_ALLOCATE WHERE T1.ALLOCATE_CODE = ALLOCATE_CODE AND BUDGET_YEAR = '" + budgetYearFilter + "') AS ALLOCATE_NAME ");
			sb.append(",T1.PROVINCE_ID, CASE WHEN SUBSTR(CAST(T1.ALLOCATE_CODE AS VARCHAR(8)), 3, LENGTH(CAST(T1.ALLOCATE_CODE AS VARCHAR(8)))) = '777777' ");
			sb.append("OR SUBSTR(CAST(T1.ALLOCATE_CODE AS VARCHAR(8)), 3, LENGTH(CAST(T1.ALLOCATE_CODE AS VARCHAR(8)))) = '888888' ");
			sb.append("OR SUBSTR(CAST(T1.ALLOCATE_CODE AS VARCHAR(8)), 3, LENGTH(CAST(T1.ALLOCATE_CODE AS VARCHAR(8)))) = '999999' ");
			sb.append("THEN T1.ALLOCATE_CODE ELSE (SELECT DISTINCT PARENT_CODE FROM "+ this.schema + ".ACC6_ALLOCATE WHERE T1.ALLOCATE_CODE = ALLOCATE_CODE AND BUDGET_YEAR = '" + budgetYearFilter + "') END AS ALLOC_PARENT_CODE ");
			sb.append("FROM "+ this.schema + ".ACC6_TRANSACTION T1,"+ this.schema + ".ACC6_EXPENSE_TRANSFER T2 ");
			sb.append("WHERE T1.TRANSACTION_TIMESTAMP = T2.TRANSACTION_TIMESTAMP ");
			sb.append("AND T1.TRANSACTION_TYPE = 'T' AND T1.REASON_CODE = '00' " + condition + " ");
			sb.append("UNION ALL ");
			sb.append("SELECT '" + ui.getUserId() + "','" + provinceIdFilter + "','" + budgetYearFilter + "','" + allocateCodeFilter + "','" + allocateCodeLvl2Filter + "'");
			sb.append(",TO_CHAR((SELECT DISTINCT CHEQUE_DATE FROM "+ this.schema + ".ACC6_EXPENSE_CHEQUE WHERE T1.TRANSACTION_TIMESTAMP = TRANSACTION_TIMESTAMP),'DD/MM/YYYY') AS EXPENSE_DATE ,T2.DOC_NO, T2.REF_NO ");
			sb.append(",(SELECT DISTINCT EXPENSE_DESC FROM "+ this.schema + ".ACC6_EXPENSE WHERE T2.DOC_NO = DOC_NO) AS EXPENSE_DESC, T1.EXPENSE_AMOUNT, 0 AS INCOME_AMOUNT ");
			sb.append(",T1.ALLOCATE_CODE,(SELECT DISTINCT ALLOCATE_NAME FROM "+ this.schema + ".ACC6_ALLOCATE WHERE T1.ALLOCATE_CODE = ALLOCATE_CODE AND BUDGET_YEAR = '" + budgetYearFilter + "') AS ALLOCATE_NAME ");
			sb.append(",T1.PROVINCE_ID, CASE WHEN  SUBSTR(CAST(T1.ALLOCATE_CODE AS VARCHAR(8)), 3, LENGTH(CAST(T1.ALLOCATE_CODE AS VARCHAR(8)))) = '777777' ");
			sb.append("OR SUBSTR(CAST(T1.ALLOCATE_CODE AS VARCHAR(8)), 3, LENGTH(CAST(T1.ALLOCATE_CODE AS VARCHAR(8)))) = '888888' ");
			sb.append("OR SUBSTR(CAST(T1.ALLOCATE_CODE AS VARCHAR(8)), 3, LENGTH(CAST(T1.ALLOCATE_CODE AS VARCHAR(8)))) = '999999' ");
			sb.append("THEN T1.ALLOCATE_CODE ELSE (SELECT DISTINCT PARENT_CODE FROM "+ this.schema + ".ACC6_ALLOCATE WHERE T1.ALLOCATE_CODE = ALLOCATE_CODE AND BUDGET_YEAR = '" + budgetYearFilter + "') END AS ALLOC_PARENT_CODE ");
			sb.append("FROM "+ this.schema + ".ACC6_TRANSACTION T1,"+ this.schema + ".ACC6_EXPENSE_CHEQUE T2 ");
			sb.append("WHERE T1.TRANSACTION_TIMESTAMP = T2.TRANSACTION_TIMESTAMP ");
			sb.append("AND T1.TRANSACTION_TYPE = 'C' AND T1.REASON_CODE = '00' AND T1.CHEQUE_DATE IS NOT NULL " + condition + " ");
			sb.append("UNION ALL ");
			sb.append("SELECT '" + ui.getUserId() + "','" + provinceIdFilter + "','" + budgetYearFilter + "','" + allocateCodeFilter + "','" + allocateCodeLvl2Filter + "'");
			sb.append(",TO_CHAR((SELECT DISTINCT CHEQUE_DATE FROM "+ this.schema + ".ACC6_CHEQUE WHERE T2.DOC_NO = DOC_NO),'DD/MM/YYYY') AS EXPENSE_DATE ,T2.DOC_NO, T3.REF_NO ");
			sb.append(",(SELECT DISTINCT CHEQUE_DESC FROM "+ this.schema + ".ACC6_CHEQUE WHERE T2.DOC_NO = DOC_NO) AS EXPENSE_DESC, T1.EXPENSE_AMOUNT, 0 AS INCOME_AMOUNT ");
			sb.append(",T1.ALLOCATE_CODE,(SELECT DISTINCT ALLOCATE_NAME FROM "+ this.schema + ".ACC6_ALLOCATE WHERE T1.ALLOCATE_CODE = ALLOCATE_CODE AND BUDGET_YEAR = '" + budgetYearFilter + "') AS ALLOCATE_NAME ");
			sb.append(",T1.PROVINCE_ID, CASE WHEN  SUBSTR(CAST(T1.ALLOCATE_CODE AS VARCHAR(8)), 3, LENGTH(CAST(T1.ALLOCATE_CODE AS VARCHAR(8)))) = '777777' ");
			sb.append("OR SUBSTR(CAST(T1.ALLOCATE_CODE AS VARCHAR(8)), 3, LENGTH(CAST(T1.ALLOCATE_CODE AS VARCHAR(8)))) = '888888' ");
			sb.append("OR SUBSTR(CAST(T1.ALLOCATE_CODE AS VARCHAR(8)), 3, LENGTH(CAST(T1.ALLOCATE_CODE AS VARCHAR(8)))) = '999999' ");
			sb.append("THEN T1.ALLOCATE_CODE ELSE (SELECT DISTINCT PARENT_CODE FROM "+ this.schema + ".ACC6_ALLOCATE WHERE T1.ALLOCATE_CODE = ALLOCATE_CODE AND BUDGET_YEAR = '" + budgetYearFilter + "') END AS ALLOC_PARENT_CODE ");
			sb.append("FROM "+ this.schema + ".ACC6_TRANSACTION T1,"+ this.schema + ".ACC6_CHEQUE T2 , "+ this.schema + ".ACC6_CHEQUE_DETAIL T3 ");
			sb.append("WHERE T1.TRANSACTION_TIMESTAMP = T3.TRANSACTION_TIMESTAMP AND T2.DOC_NO = T3.DOC_NO AND T1.TRANSACTION_TYPE = 'C'  ");
			sb.append("AND T1.TRANSACTION_TYPE = 'C' AND T1.REASON_CODE = '00' AND T1.CHEQUE_DATE IS NOT NULL " + condition + " ");
			sb.append("UNION ALL ");
			sb.append("SELECT '" + ui.getUserId() + "','" + provinceIdFilter + "','" + budgetYearFilter + "','" + allocateCodeFilter + "','" + allocateCodeLvl2Filter + "'");
			sb.append(",TO_CHAR(T2.EXPENSE_DATE,'DD/MM/YYYY') AS EXPENSE_DATE,T2.DOC_NO, T2.REF_NO ");
			sb.append(",(SELECT DISTINCT EXPENSE_DESC FROM "+ this.schema + ".ACC6_EXPENSE WHERE T2.DOC_NO = DOC_NO) AS EXPENSE_DESC, T1.EXPENSE_AMOUNT, 0 AS INCOME_AMOUNT ");
			sb.append(",T1.ALLOCATE_CODE,(SELECT DISTINCT ALLOCATE_NAME FROM "+ this.schema + ".ACC6_ALLOCATE WHERE T1.ALLOCATE_CODE = ALLOCATE_CODE AND BUDGET_YEAR = '" + budgetYearFilter + "') AS ALLOCATE_NAME ");
			sb.append(",T1.PROVINCE_ID,CASE WHEN SUBSTR(CAST(T1.ALLOCATE_CODE AS VARCHAR(8)), 3, LENGTH(CAST(T1.ALLOCATE_CODE AS VARCHAR(8)))) = '777777' ");
			sb.append("OR SUBSTR(CAST(T1.ALLOCATE_CODE AS VARCHAR(8)), 3, LENGTH(CAST(T1.ALLOCATE_CODE AS VARCHAR(8)))) = '888888' ");
			sb.append("OR SUBSTR(CAST(T1.ALLOCATE_CODE AS VARCHAR(8)), 3, LENGTH(CAST(T1.ALLOCATE_CODE AS VARCHAR(8)))) = '999999' ");
			sb.append("THEN T1.ALLOCATE_CODE ELSE (SELECT DISTINCT PARENT_CODE FROM "+ this.schema + ".ACC6_ALLOCATE WHERE T1.ALLOCATE_CODE = ALLOCATE_CODE AND BUDGET_YEAR = '" + budgetYearFilter + "') END AS ALLOC_PARENT_CODE ");
			sb.append("FROM "+ this.schema + ".ACC6_TRANSACTION T1,"+ this.schema + ".ACC6_EXPENSE_OTHER T2 ");
			sb.append("WHERE T1.TRANSACTION_TIMESTAMP = T2.TRANSACTION_TIMESTAMP ");
			sb.append("AND T1.TRANSACTION_TYPE = 'P' AND T1.REASON_CODE = '00' " + condition + " ");
			sb.append("UNION ALL ");
			sb.append("SELECT '" + ui.getUserId() + "','" + provinceIdFilter + "','" + budgetYearFilter + "','" + allocateCodeFilter + "','" + allocateCodeLvl2Filter + "'");
			sb.append(",TO_CHAR(T2.INCOME_DATE,'DD/MM/YYYY') AS EXPENSE_DATE,T2.DOC_NO, T2.REF_NO ,T2.INCOME_DESC, 0 AS EXPENSE_AMOUNT,T1.INCOME_AMOUNT");
			sb.append(",T1.ALLOCATE_CODE,(SELECT DISTINCT ALLOCATE_NAME FROM "+ this.schema + ".ACC6_ALLOCATE WHERE T1.ALLOCATE_CODE = ALLOCATE_CODE AND BUDGET_YEAR = '" + budgetYearFilter + "') AS ALLOCATE_NAME ");
			sb.append(",T1.PROVINCE_ID,CASE WHEN SUBSTR(CAST(T1.ALLOCATE_CODE AS VARCHAR(8)), 3, LENGTH(CAST(T1.ALLOCATE_CODE AS VARCHAR(8)))) = '777777' ");
			sb.append("OR SUBSTR(CAST(T1.ALLOCATE_CODE AS VARCHAR(8)), 3, LENGTH(CAST(T1.ALLOCATE_CODE AS VARCHAR(8)))) = '888888' ");
			sb.append("OR SUBSTR(CAST(T1.ALLOCATE_CODE AS VARCHAR(8)), 3, LENGTH(CAST(T1.ALLOCATE_CODE AS VARCHAR(8)))) = '999999' ");
			sb.append("THEN T1.ALLOCATE_CODE ELSE  (SELECT DISTINCT PARENT_CODE FROM "+ this.schema + ".ACC6_ALLOCATE WHERE T1.ALLOCATE_CODE = ALLOCATE_CODE AND BUDGET_YEAR = '" + budgetYearFilter + "') END AS ALLOC_PARENT_CODE ");
			sb.append("FROM "+ this.schema + ".ACC6_TRANSACTION T1,"+ this.schema + ".ACC6_INCOME T2 ");
			sb.append("WHERE T1.TRANSACTION_TIMESTAMP = T2.TRANSACTION_TIMESTAMP ");
			sb.append("AND T1.TRANSACTION_TYPE IN ('R','O','F') AND T1.REASON_CODE = '00' " + condition + " ");
			sb.append("UNION ALL ");
			condition = condition.replaceAll("T1.", "");
			sb.append("SELECT '" + ui.getUserId() + "','" + provinceIdFilter + "','" + budgetYearFilter + "','" + allocateCodeFilter + "','" + allocateCodeLvl2Filter + "'");
			sb.append(",TO_CHAR(INCOME_DATE,'DD/MM/YYYY') AS EXPENSE_DATE,'' AS DOC_NO, REF_NO ,'จาก SAP' AS INCOME_DESC, 0 AS EXPENSE_AMOUNT,INCOME_AMOUNT ");
			sb.append(",ALLOCATE_CODE,(SELECT DISTINCT ALLOCATE_NAME FROM "+ this.schema + ".ACC6_ALLOCATE WHERE ACC6_TRANSACTION.ALLOCATE_CODE = ALLOCATE_CODE AND BUDGET_YEAR = '" + budgetYearFilter + "') AS ALLOCATE_NAME ");
			sb.append(",PROVINCE_ID,CASE WHEN SUBSTR(CAST(ACC6_TRANSACTION.ALLOCATE_CODE AS VARCHAR(8)), 3, LENGTH(CAST(ACC6_TRANSACTION.ALLOCATE_CODE AS VARCHAR(8)))) = '777777' ");
			sb.append("OR SUBSTR(CAST(ACC6_TRANSACTION.ALLOCATE_CODE AS VARCHAR(8)), 3, LENGTH(CAST(ACC6_TRANSACTION.ALLOCATE_CODE AS VARCHAR(8)))) = '888888' ");
			sb.append("OR SUBSTR(CAST(ACC6_TRANSACTION.ALLOCATE_CODE AS VARCHAR(8)), 3, LENGTH(CAST(ACC6_TRANSACTION.ALLOCATE_CODE AS VARCHAR(8)))) = '999999' ");
			sb.append("THEN ACC6_TRANSACTION.ALLOCATE_CODE ELSE  (SELECT DISTINCT PARENT_CODE FROM "+ this.schema + ".ACC6_ALLOCATE WHERE ACC6_TRANSACTION.ALLOCATE_CODE = ALLOCATE_CODE  AND BUDGET_YEAR = '" + budgetYearFilter + "') END AS ALLOC_PARENT_CODE ");
			sb.append("FROM "+ this.schema + ".ACC6_TRANSACTION ");
			sb.append("WHERE TRANSACTION_TYPE = 'S' AND REASON_CODE = '00' " + condition + " ");
			if (Integer.parseInt(budgetYearFilter) <= 2556) {
				sb.append("UNION ALL ");
				sb.append("SELECT '" + ui.getUserId() + "','" + provinceIdFilter + "','" + budgetYearFilter + "','" + allocateCodeFilter + "','" + allocateCodeLvl2Filter + "'");
				sb.append(",TO_CHAR(INCOME_DATE,'DD/MM/YYYY') AS EXPENSE_DATE,'', REF_NO,'ยอดยกมา', 0 AS EXPENSE_AMOUNT,INCOME_AMOUNT ");
				sb.append(",ALLOCATE_CODE,(SELECT DISTINCT ALLOCATE_NAME FROM "+ this.schema + ".ACC6_ALLOCATE WHERE ACC6_TRANSACTION.ALLOCATE_CODE = ALLOCATE_CODE AND BUDGET_YEAR = '" + budgetYearFilter + "') AS ALLOCATE_NAME ");
				sb.append(",PROVINCE_ID,CASE WHEN  SUBSTR(CAST(ACC6_TRANSACTION.ALLOCATE_CODE AS VARCHAR(8)), 3, LENGTH(CAST(ACC6_TRANSACTION.ALLOCATE_CODE AS VARCHAR(8)))) = '777777' ");
				sb.append("OR SUBSTR(CAST(ACC6_TRANSACTION.ALLOCATE_CODE AS VARCHAR(8)), 3, LENGTH(CAST(ACC6_TRANSACTION.ALLOCATE_CODE AS VARCHAR(8)))) = '888888' ");
				sb.append("OR SUBSTR(CAST(ACC6_TRANSACTION.ALLOCATE_CODE AS VARCHAR(8)), 3, LENGTH(CAST(ACC6_TRANSACTION.ALLOCATE_CODE AS VARCHAR(8)))) = '999999' ");
				sb.append("THEN ACC6_TRANSACTION.ALLOCATE_CODE ELSE (SELECT DISTINCT PARENT_CODE FROM ACC6_ALLOCATE WHERE ACC6_TRANSACTION.ALLOCATE_CODE = ALLOCATE_CODE AND BUDGET_YEAR = '" + budgetYearFilter + "') END AS ALLOC_PARENT_CODE ");
				sb.append("FROM "+ this.schema + ".ACC6_TRANSACTION ");
				sb.append("WHERE TRANSACTION_TYPE = 'B' AND REASON_CODE = '00' " + condition + " ");
			} else {// ถ้าเป็นปีงบที่มากกว่าปี 2556 ยอดยกมาจะเป็น 0 เนื่องจากหลังจากปีงบ 2556 จะไม่มียอดยกมา
				sb.append("UNION ALL ");
				sb.append("SELECT '" + ui.getUserId() + "','" + provinceIdFilter + "','" + budgetYearFilter + "','" + allocateCodeFilter + "','" + allocateCodeLvl2Filter + "'");
				sb.append(",TO_CHAR(INCOME_DATE,'DD/MM/YYYY') AS EXPENSE_DATE,'', REF_NO,'ยอดยกมา', 0 AS EXPENSE_AMOUNT,0 AS INCOME_AMOUNT ");
				sb.append(",ALLOCATE_CODE,(SELECT DISTINCT ALLOCATE_NAME FROM "+ this.schema + ".ACC6_ALLOCATE WHERE ACC6_TRANSACTION.ALLOCATE_CODE = ALLOCATE_CODE AND BUDGET_YEAR = '" + budgetYearFilter + "') AS ALLOCATE_NAME ");
				sb.append(",PROVINCE_ID,CASE WHEN  SUBSTR(CAST(ACC6_TRANSACTION.ALLOCATE_CODE AS VARCHAR(8)), 3, LENGTH(CAST(ACC6_TRANSACTION.ALLOCATE_CODE AS VARCHAR(8)))) = '777777' ");
				sb.append("OR SUBSTR(CAST(ACC6_TRANSACTION.ALLOCATE_CODE AS VARCHAR(8)), 3, LENGTH(CAST(ACC6_TRANSACTION.ALLOCATE_CODE AS VARCHAR(8)))) = '888888' ");
				sb.append("OR SUBSTR(CAST(ACC6_TRANSACTION.ALLOCATE_CODE AS VARCHAR(8)), 3, LENGTH(CAST(ACC6_TRANSACTION.ALLOCATE_CODE AS VARCHAR(8)))) = '999999' ");
				sb.append("THEN ACC6_TRANSACTION.ALLOCATE_CODE ELSE (SELECT DISTINCT PARENT_CODE FROM ACC6_ALLOCATE WHERE ACC6_TRANSACTION.ALLOCATE_CODE = ALLOCATE_CODE AND BUDGET_YEAR = '" + budgetYearFilter + "') END AS ALLOC_PARENT_CODE ");
				sb.append("FROM "+ this.schema + ".ACC6_TRANSACTION ");
				sb.append("WHERE TRANSACTION_TYPE = 'B' AND REASON_CODE = '00' " + condition + " ");
				/*List ls = new ArrayList();
				List lsProvince = new ArrayList();
				String condition2 = ""; // for budgetYear > 2556
				String allocCateCode2 = "";
				String sql = "";
				if (!allocateCodeLvl2Filter.equals("all")) {
					allocateCodeLvl2 = String.valueOf(Integer.parseInt(allocateCodeLvl2Filter.substring(0, 2)) - 1) + allocateCodeLvl2Filter.substring(2, 6);
					condition2 = " ALLOCATE_CODE LIKE '" + allocateCodeLvl2 + "%'";
				} else {
					allocateCode = String.valueOf(Integer.parseInt(allocateCodeFilter.substring(0, 2)) - 1) + allocateCodeFilter.substring(2, 4);
					condition2 = " ALLOCATE_CODE LIKE '" + allocateCode + "%'";
				}
				sql = "SELECT PROVINCE_ID FROM "+ this.schema + ".PROVINCE ";
				if (!provinceIdFilter.equals("all")) {
					sql += " WHERE PROVINCE_ID = '" + provinceIdFilter + "'";
				}
				sql += " ORDER BY PROVINCE_NAME ASC ";
				lsProvince = dao.listDataSql(sql);
				ls = dao.listDataSql("SELECT ALLOCATE_CODE FROM "+ this.schema + ".ACC6_ALLOCATE WHERE " + condition2 + " AND ALLOCATE_APPLY = 'Y' ORDER BY ALLOCATE_CODE ASC ");
				for (int p=0;p<lsProvince.size();p++) {
					Map provinceMap = (Map)lsProvince.get(p);
					for (int i=0;i<ls.size();i++) {
						Map map = (Map) ls.get(i);
						String allocCode = dao.chkData(map.get("ALLOCATE_CODE"));
						int budgetYear = Integer.parseInt(allocCode.substring(0, 2));
						allocCode = (budgetYear) +  allocCode.substring(2, allocCode.length());
						sb.append("UNION ALL ");
						sb.append("SELECT '" + ui.getUserId() + "','" + provinceIdFilter + "','" + budgetYearFilter + "','" + allocateCodeFilter + "','" + allocateCodeLvl2Filter + "'");
						sb.append(",CAST('01/10/2012' AS CHAR(10)) AS EXPENSE_DATE,'','' AS REF_NO,'ยอดยกมา', 0 AS EXPENSE_AMOUNT, ");
						sb.append("((SELECT NVL(SUM(INCOME_AMOUNT),0) AS SUM_INCOME_AMOUNT FROM "+this.schema+".ACC6_TRANSACTION WHERE TRANSACTION_TYPE IN ('B','S','R','O','F') AND ALLOCATE_CODE = '" + allocCode + "' ");
						sb.append(" AND REASON_CODE = '00'  AND PROVINCE_ID = '" + dao.chkData(provinceMap.get("PROVINCE_ID")) + "') - ");
						sb.append("(SELECT NVL(SUM(EXPENSE_AMOUNT),0) AS SUM_EXPENSE_AMOUNT FROM "+this.schema+".ACC6_TRANSACTION WHERE TRANSACTION_TYPE IN ('T','C','P') AND ALLOCATE_CODE = '" + allocCode + "' ");
						sb.append(" AND REASON_CODE = '00'  AND PROVINCE_ID = '" + dao.chkData(provinceMap.get("PROVINCE_ID")) + "')) AS INCOME_AMOUNT ");
						sb.append(",ALLOCATE_CODE, ALLOCATE_NAME ,'" + dao.chkData(provinceMap.get("PROVINCE_ID")) + "' AS PROVINCE_ID,PARENT_CODE ");
						allocCateCode2 = budgetYearFilter.substring(2, 4) + allocCode.substring(2, allocCode.length());
						sb.append("FROM "+this.schema+".ACC6_ALLOCATE WHERE ALLOCATE_CODE = '" + allocCateCode2 + "'");
					}
				}*/
			}
			//System.out.println("sb.toString() >> " + sb.toString());
			dao.execDataSql(sb.toString());
			
			this.url = "http://"+this.reportHost+"/BOE/CrystalReports/viewrpt.cwr?&id="+idReport+"&sRefresh=Y" +
		    "&apsuser=" + this.reportUser + "&apspassword=" + this.reportPassword + "" +
		    "&apsauthtype=" + this.reportType + "&promptex-userId=" + ui.getUserId();
			
			response.sendRedirect(this.url);
			request.setAttribute("lsBudgetYear", this.dataUtil(dao).lsBudgetYear());
			conn.commit();
			
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
				this.url = "";
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	
	private String ACC6REPORT004(HttpServletRequest request, HttpServletResponse response) {
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			UserInfo ui = this.user(request);
			Acc6ReportProvinceSummaryBean apsb = new Acc6ReportProvinceSummaryBean();
			
			String provinceIdFilter = this.getAttr(request, "provinceIdFilter");
			String budgetYearFilter = this.getAttr(request, "budgetYearFilter"); 
			String startDateFilter = this.getAttr(request, "startDateFilter");
			String endDateFilter = this.getAttr(request, "endDateFilter");
			String allocateYearFilter = this.getAttr(request, "allocateYearFilter");
			String type = this.getAttr(request, "type");
			String condition = "";
			int startBudgetYear = 2;
			int endBudgetYear = 1;
			//String allocLvl = "3";
			StringBuffer sb = new StringBuffer();
			
			if (provinceIdFilter.equals("")) {
				provinceIdFilter = "all";
			}
			apsb.setProvinceIdFilter(provinceIdFilter);
			apsb.setBudgetYearFilter(budgetYearFilter);
			apsb.setStartDateFilter(this.dateUtil().changeFormatDate2YYYYMMDD(startDateFilter, "/", ""));
			apsb.setEndDateFilter(this.dateUtil().changeFormatDate2YYYYMMDD(endDateFilter, "/", ""));
			apsb.setAllocateYearFilter(allocateYearFilter);
			apsb.setType(type);
			
			sb.append("DELETE FROM "+this.schema+".ACC6_REPORT_PROVINCE_SUMMARY WHERE USER_ID = '" + ui.getUserId() + "' ");
			dao.execDataSql(sb.toString());
			if (ConfigBundle.getValue("DEPLOY_TYPE").equals("P")) {
				startDateFilter = this.dateUtil().changeDateToDB(startDateFilter);
				endDateFilter = this.dateUtil().changeDateToDB(endDateFilter);
				condition = " AND TO_DATE(INCOME_DATE,'DD.MM.YYYY') >= " + startDateFilter + " AND TO_DATE(INCOME_DATE,'DD.MM.YYYY') <= " + endDateFilter + " ";
			} else {
				startDateFilter =  this.dateUtil().changeFormatDate2YYYYMMDD(startDateFilter, "/", "");
				endDateFilter = this.dateUtil().changeFormatDate2YYYYMMDD(endDateFilter, "/", "");
				condition = " AND TO_CHAR(INCOME_DATE,'YYYYMMDD') >= '" + startDateFilter + "' AND TO_CHAR(INCOME_DATE,'YYYYMMDD') <= '" + endDateFilter + "' ";
			}
			sb.setLength(0);
			sb.append("INSERT INTO "+ this.schema + ".ACC6_REPORT_PROVINCE_SUMMARY (USER_ID,PROVINCE_ID_FILTER,BUDGET_YEAR_FILTER,START_DATE_FILTER ");
			sb.append(",END_DATE_FILTER,ALLOCATE_YEAR_FILTER,TYPE,PROVINCE_ID,ALLOCATE_CODE,ALLOCATE_NAME,PARENT_CODE,BUDGET_YEAR ");
			sb.append(",INCOME_AMOUNT_TYPE_B,INCOME_AMOUNT_TYPE_S,EXPENSE_AMOUNT,NHSO_ZONE) ");
			sb.append("SELECT * FROM (SELECT '" + ui.getUserId() + "' AS USER_ID,'" + apsb.getProvinceIdFilter() + "' AS PROVINCE_ID_FILTER");
			sb.append(",'" + apsb.getBudgetYearFilter() + "' AS BUDGET_YEAR_FILTER,'" + apsb.getStartDateFilter() + "' AS START_DATE_FILTER,'" + apsb.getEndDateFilter() + "' AS END_DATE_FILTER" );
			sb.append(",'" + apsb.getAllocateYearFilter() + "' AS ALLOCATE_YEAR_FILTER,'" + type + "' AS TYPE,T2.PROVINCE_ID,CAST(T1.ALLOCATE_CODE AS VARCHAR(8)) AS ALLOCATE_CODE,T1.ALLOCATE_NAME ");
			if (Integer.parseInt(allocateYearFilter) >= 2556) {
				sb.append(",CASE WHEN SUBSTR(T1.ALLOCATE_CODE, 3, LENGTH(T1.ALLOCATE_CODE)) = '777777' OR SUBSTR(T1.ALLOCATE_CODE, 3, LENGTH(T1.ALLOCATE_CODE)) = '888888' ");
				sb.append("OR SUBSTR(T1.ALLOCATE_CODE, 3, LENGTH(T1.ALLOCATE_CODE)) = '999999' THEN T1.ALLOCATE_CODE ELSE T1.PARENT_CODE END AS PARENT_CODE ");
			} else if (Integer.parseInt(allocateYearFilter) < 2556) {
				sb.append(",CASE WHEN SUBSTR(T1.ALLOCATE_CODE, 3, LENGTH(T1.ALLOCATE_CODE)) = '777777' OR SUBSTR(T1.ALLOCATE_CODE, 3, LENGTH(T1.ALLOCATE_CODE)) = '888888' ");
				sb.append("OR SUBSTR(T1.ALLOCATE_CODE, 3, LENGTH(T1.ALLOCATE_CODE)) = '999999' THEN T1.ALLOCATE_CODE ELSE T1.ALLOCATE_CODE END AS PARENT_CODE ");
			}
			sb.append(",T1.BUDGET_YEAR ");
			if (Integer.parseInt(budgetYearFilter) < 2557) {
				sb.append(",(SELECT NVL(SUM(INCOME_AMOUNT),0) AS SUM_INCOME_AMOUNT FROM "+this.schema+".ACC6_TRANSACTION WHERE TRANSACTION_TYPE = 'B' AND REASON_CODE = '00' ");
				sb.append("AND T1.ALLOCATE_CODE = ALLOCATE_CODE AND T2.PROVINCE_ID = PROVINCE_ID " + condition + " AND BUDGET_YEAR = '" + apsb.getAllocateYearFilter() +"' ) AS INCOME_AMOUNT_TYPE_B ");
			} else {
				int iBudgetYearAD = Integer.parseInt(budgetYearFilter) - 543;
				sb.append(",(SELECT NVL(SUM(INCOME_AMOUNT),0) AS SUM_INCOME_AMOUNT FROM "+this.schema+".ACC6_TRANSACTION WHERE TRANSACTION_TYPE IN ('B','S','F','R','O') AND REASON_CODE = '00' ");
				sb.append("AND CAST(ALLOCATE_CODE AS VARCHAR2(8)) LIKE CASE WHEN LENGTH(CAST(T1.ALLOCATE_CODE AS VARCHAR2(8))) = 4 THEN CAST(T1.ALLOCATE_CODE AS VARCHAR2(8)) || '%' ");
				sb.append("WHEN SUBSTR(CAST(T1.ALLOCATE_CODE AS VARCHAR2(8)),7,2) = '00' THEN SUBSTR(CAST(T1.ALLOCATE_CODE AS VARCHAR2(8)),1,6) || '%' ");
				sb.append("ELSE CAST(T1.ALLOCATE_CODE AS VARCHAR2(8)) END AND T2.PROVINCE_ID = PROVINCE_ID AND BUDGET_YEAR = '" + apsb.getAllocateYearFilter() +"' ");
				sb.append("AND TO_CHAR(INCOME_DATE,'YYYYMMDD') >= '" + (iBudgetYearAD - startBudgetYear) + "1001' AND TO_CHAR(INCOME_DATE,'YYYYMMDD') <= '" + (iBudgetYearAD - endBudgetYear) + "0930' ) - ");
				sb.append("((SELECT NVL(SUM(EXPENSE_AMOUNT),0) AS SUM_EXPENSE_AMOUNT FROM "+this.schema+".ACC6_TRANSACTION WHERE TRANSACTION_TYPE IN ('T','P') AND REASON_CODE = '00' ");
				sb.append("AND CAST(ALLOCATE_CODE AS VARCHAR2(8)) LIKE  CASE WHEN LENGTH(CAST(T1.ALLOCATE_CODE AS VARCHAR2(8))) = 4 THEN CAST(T1.ALLOCATE_CODE AS VARCHAR2(8)) || '%' ");
				sb.append("WHEN SUBSTR(CAST(T1.ALLOCATE_CODE AS VARCHAR2(8)),7,2) = '00' THEN SUBSTR(CAST(T1.ALLOCATE_CODE AS VARCHAR2(8)),1,6) || '%' ");
				sb.append("ELSE CAST(T1.ALLOCATE_CODE AS VARCHAR2(8)) END AND T2.PROVINCE_ID = PROVINCE_ID  AND TO_CHAR(EXPENSE_DATE,'YYYYMMDD') >= '" + (iBudgetYearAD - startBudgetYear) + "1001' AND TO_CHAR(EXPENSE_DATE,'YYYYMMDD') <= '" + (iBudgetYearAD - endBudgetYear) + "0930' ");
				sb.append("AND BUDGET_YEAR = '" + apsb.getAllocateYearFilter() +"')  +  (SELECT NVL(SUM(EXPENSE_AMOUNT),0) AS SUM_EXPENSE_AMOUNT FROM "+this.schema+".ACC6_TRANSACTION WHERE TRANSACTION_TYPE IN ('C') AND REASON_CODE = '00' ");
				sb.append("AND CAST(ALLOCATE_CODE AS VARCHAR2(8)) LIKE CASE WHEN LENGTH(CAST(T1.ALLOCATE_CODE AS VARCHAR2(8))) = 4 THEN CAST(T1.ALLOCATE_CODE AS VARCHAR2(8)) || '%' ");
				sb.append("WHEN SUBSTR(CAST(T1.ALLOCATE_CODE AS VARCHAR2(8)),7,2) = '00' THEN SUBSTR(CAST(T1.ALLOCATE_CODE AS VARCHAR2(8)),1,6) || '%' ELSE CAST(T1.ALLOCATE_CODE AS VARCHAR2(8)) END ");
				sb.append("AND T2.PROVINCE_ID = PROVINCE_ID  AND TO_CHAR(CHEQUE_DATE,'YYYYMMDD') >= '" + (iBudgetYearAD - startBudgetYear) + "1001' ");
				sb.append("AND TO_CHAR(CHEQUE_DATE,'YYYYMMDD') <= '" + (iBudgetYearAD - endBudgetYear) + "0930'  AND BUDGET_YEAR = '" + apsb.getAllocateYearFilter() +"' )) AS INCOME_AMOUNT_TYPE_B ");
			}
			
			sb.append(",(SELECT NVL(SUM(INCOME_AMOUNT),0) AS SUM_INCOME_AMOUNT FROM "+this.schema+".ACC6_TRANSACTION WHERE TRANSACTION_TYPE IN ('S','F','R','O') AND REASON_CODE = '00' ");
			sb.append("AND T1.ALLOCATE_CODE = ALLOCATE_CODE AND T2.PROVINCE_ID = PROVINCE_ID " + condition + " AND BUDGET_YEAR = '" + apsb.getAllocateYearFilter() +"' ) AS INCOME_AMOUNT_TYPE_S ");
			sb.append(",(SELECT NVL(SUM(EXPENSE_AMOUNT),0) AS SUM_EXPENSE_AMOUNT FROM "+this.schema+".ACC6_TRANSACTION WHERE TRANSACTION_TYPE IN ('T','P') AND REASON_CODE = '00' ");
			sb.append("AND T1.ALLOCATE_CODE = ALLOCATE_CODE AND T2.PROVINCE_ID = PROVINCE_ID " + condition.replace("INCOME_DATE", "EXPENSE_DATE") + " AND BUDGET_YEAR = '" + apsb.getAllocateYearFilter() +"')  +  ");
			sb.append(" (SELECT NVL(SUM(EXPENSE_AMOUNT),0) AS SUM_EXPENSE_AMOUNT FROM "+this.schema+".ACC6_TRANSACTION WHERE TRANSACTION_TYPE IN ('C') AND REASON_CODE = '00' ");
			sb.append("AND T1.ALLOCATE_CODE = ALLOCATE_CODE AND T2.PROVINCE_ID = PROVINCE_ID " + condition.replace("INCOME_DATE", "CHEQUE_DATE") + " AND BUDGET_YEAR = '" + apsb.getAllocateYearFilter() +"' ) AS EXPENSE_AMOUNT ");
			sb.append(",T2.NHSO_ZONE FROM "+this.schema+".ACC6_ALLOCATE T1, "+this.schema+".PROVINCE T2 ");
			/*if (Integer.parseInt(allocateYearFilter) < 2556 || type.equals("2")) {
				allocLvl = "2";
			}
			(LENGTH(T1.ALLOCATE_CODE)) = 8 AND T1.ALLOCATE_LEVEL = " + allocLvl +" AND
			*/
			sb.append("WHERE (T2.NHSO_ZONE != 14 AND T2.NHSO_ZONE != 15) ");
			if (type.equals("0")) {
				sb.append("OR ( SUBSTR(T1.ALLOCATE_CODE, 3, LENGTH(T1.ALLOCATE_CODE)) = '777777' ");
				sb.append("OR SUBSTR(T1.ALLOCATE_CODE, 3, LENGTH(T1.ALLOCATE_CODE)) = '888888' ");
				sb.append("OR SUBSTR(T1.ALLOCATE_CODE, 3, LENGTH(T1.ALLOCATE_CODE)) = '999999' )");
			} else if (type.equals("1")) {
				sb.append("OR ( SUBSTR(T1.ALLOCATE_CODE, 3, LENGTH(T1.ALLOCATE_CODE)) = '777777' ");
				sb.append("OR SUBSTR(T1.ALLOCATE_CODE, 3, LENGTH(T1.ALLOCATE_CODE)) = '999999' )");
			} else if (type.equals("2")) {
				sb.append("AND SUBSTR(T1.ALLOCATE_CODE, 3, LENGTH(T1.ALLOCATE_CODE)) = '888888' ");
			}

			if (!provinceIdFilter.equals("all") || ui.getRoleId().equals("3")){	
				sb.append(" AND T2.PROVINCE_ID = '" + provinceIdFilter + "' ");
			} else if (ui.getRoleId().equals("2") && provinceIdFilter.equals("all")) {
				sb.append(" AND T2.NHSO_ZONE = '" + ui.getAreaCode() + "' ");
			}
			sb.append(" AND T1.BUDGET_YEAR = '" + allocateYearFilter + "' ");
			sb.append(") WHERE (NHSO_ZONE != 14 AND NHSO_ZONE != 15) AND BUDGET_YEAR = '" + allocateYearFilter + "' ");
			if (!provinceIdFilter.equals("all") || ui.getRoleId().equals("3")){	
				sb.append(" AND PROVINCE_ID = '" + provinceIdFilter + "' ");
			} else if (ui.getRoleId().equals("2") && provinceIdFilter.equals("all")) {
				sb.append(" AND NHSO_ZONE = '" + ui.getAreaCode() + "' ");
			}
			dao.execDataSql(sb.toString());
			this.url = "http://"+this.reportHost+"/BOE/CrystalReports/viewrpt.cwr?&id="+this.idReport1+"&sRefresh=Y" +
		    "&apsuser=" + this.reportUser + "&apspassword=" + this.reportPassword + "" +
		    "&apsauthtype=" + this.reportType + "&promptex-userId=" + ui.getUserId();
			response.sendRedirect(this.url);
			
			request.setAttribute("provinceIdFilter", provinceIdFilter);
			request.setAttribute("budgetYearFilter", budgetYearFilter);
			request.setAttribute("startDateFilter", this.getAttr(request, "startDateFilter"));
			request.setAttribute("endDateFilter", this.getAttr(request, "endDateFilter"));
			request.setAttribute("allocateYearFilter", allocateYearFilter);
			request.setAttribute("type", type);
			request.setAttribute("lsBudgetYear", this.dataUtil(dao).lsBudgetYear());
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			e.printStackTrace();
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
				this.url = "";
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}

	private String ACC6REPORT005(HttpServletRequest request, HttpServletResponse response) {
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			List ls = new ArrayList();
			List listProvacc = new ArrayList();
			UserInfo ui = this.user(request);
			Acc6ReportMoneyStatusBean amsb = new Acc6ReportMoneyStatusBean();
			
			String provinceIdFilter = this.getAttr(request, "provinceIdFilter");
			String budgetYearFilter = this.getAttr(request, "budgetYearFilter"); 
			String startDateFilter = this.getAttr(request, "startDateFilter");
			String endDateFilter = this.getAttr(request, "endDateFilter");
			String allocYearFilter = this.getAttr(request, "allocYearFilter");
			String conditionIncomeDate = "";
			String conditionExpendDate = "";
			String provaccId = "";
			StringBuffer sb = new StringBuffer();
			
			if (provinceIdFilter.equals("")) {
				provinceIdFilter = "all";
			}
			if (budgetYearFilter.equals("")) {
				budgetYearFilter = "all";
			}
			if (allocYearFilter.equals("")) {
				allocYearFilter = "all";
			}
			amsb.setUserId(ui.getUserId());
			amsb.setProvinceIdFilter(provinceIdFilter);
			amsb.setBudgetYearFilter(budgetYearFilter);
			amsb.setStartDateFilter(this.dateUtil().changeFormatDate2YYYYMMDD(startDateFilter, "/", ""));
			amsb.setEndDateFilter(this.dateUtil().changeFormatDate2YYYYMMDD(endDateFilter, "/", ""));
			
			sb.append("DELETE FROM "+this.schema+".ACC6_REPORT_MONEY_STATUS WHERE USER_ID = '" + ui.getUserId() + "' ");
			dao.execDataSql(sb.toString());

			if (ConfigBundle.getValue("DEPLOY_TYPE").equals("P")) {
				startDateFilter = this.dateUtil().changeDateToDB(startDateFilter);
				endDateFilter = this.dateUtil().changeDateToDB(endDateFilter);
				conditionIncomeDate = " AND TO_DATE(INCOME_DATE,'DD.MM.YYYY') >= " + startDateFilter + " AND TO_DATE(INCOME_DATE,'DD.MM.YYYY') <= " + endDateFilter + " ";
				conditionExpendDate = " AND TO_DATE(EXPENSE_DATE,'DD.MM.YYYY') >= " + startDateFilter + " AND TO_DATE(EXPENSE_DATE,'DD.MM.YYYY') <= " + endDateFilter + " ";
			} else {
				startDateFilter =  this.dateUtil().changeFormatDate2YYYYMMDD(startDateFilter, "/", "");
				endDateFilter = this.dateUtil().changeFormatDate2YYYYMMDD(endDateFilter, "/", "");
				conditionIncomeDate = " AND TO_CHAR(INCOME_DATE,'YYYYMMDD') >= '" + startDateFilter + "' AND TO_CHAR(INCOME_DATE,'YYYYMMDD') <= '" + endDateFilter + "' ";
				conditionExpendDate = " AND TO_CHAR(EXPENSE_DATE,'YYYYMMDD') >= '" + startDateFilter + "' AND TO_CHAR(EXPENSE_DATE,'YYYYMMDD') <= '" + endDateFilter + "' ";
			}
			ls = new ArrayList();
			ls = dao.listDataSql("SELECT DISTINCT BUDGET_YEAR FROM "+this.schema+".ACC6_ALLOCATE ORDER BY BUDGET_YEAR DESC");
			if (provinceIdFilter.equals("all")) {
				if(ui.getRoleId().equals("1")){
					listProvacc = this.dataUtil(dao).lsProvince();
				} else if (ui.getRoleId().equals("2")) {
					listProvacc = dao.listDataSql("SELECT PROVINCE_ID FROM " + this.schema + ".PROVINCE WHERE NHSO_ZONE = '" + ui.getAreaCode() + "'");
				}
				
				for (int i = 0 ; i < listProvacc.size() ; i++) {
					Map mapProvince = (Map) listProvacc.get(i);
					provaccId = dao.chkData(mapProvince.get("PROVINCE_ID"));
					if (allocYearFilter.equals("all")) {
						for (int j = 0 ; j < ls.size() ; j++ ) {
							Map map = (Map)ls.get(j);
							String budgetYear = map.get("BUDGET_YEAR").toString();
							this.ACC6REPORT005SaveData(dao, conditionIncomeDate, conditionExpendDate, budgetYear, provaccId, amsb, allocYearFilter, budgetYearFilter);
						}
					} else {
						this.ACC6REPORT005SaveData(dao, conditionIncomeDate, conditionExpendDate, allocYearFilter, provaccId, amsb, allocYearFilter, budgetYearFilter);
					}
				}
			} else {
				if (allocYearFilter.equals("all")) {
					for (int j = 0 ; j < ls.size() ; j++ ) {
						Map map = (Map)ls.get(j);
						String budgetYear = map.get("BUDGET_YEAR").toString();
						this.ACC6REPORT005SaveData(dao, conditionIncomeDate, conditionExpendDate, budgetYear, provinceIdFilter, amsb, allocYearFilter, budgetYearFilter);
					}
				} else {
					this.ACC6REPORT005SaveData(dao, conditionIncomeDate, conditionExpendDate, allocYearFilter, provinceIdFilter, amsb, allocYearFilter, budgetYearFilter);
				}
			}
			this.url = "http://"+this.reportHost+"/BOE/CrystalReports/viewrpt.cwr?&id="+this.idReport5+"&sRefresh=Y" +
		    "&apsuser=" + this.reportUser + "&apspassword=" + this.reportPassword + "" +
		    "&apsauthtype=" + this.reportType + "&promptex-userId=" + ui.getUserId();
			response.sendRedirect(this.url);
			request.setAttribute("provinceIdFilter", provinceIdFilter);
			request.setAttribute("budgetYearFilter", budgetYearFilter);
			request.setAttribute("startDateFilter", this.getAttr(request, "startDateFilter"));
			request.setAttribute("endDateFilter", this.getAttr(request, "endDateFilter"));
			request.setAttribute("lsBudgetYear", this.dataUtil(dao).lsBudgetYear());
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			e.printStackTrace();
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
				this.url = "";
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	
	private String ACC6REPORT006(HttpServletRequest request, HttpServletResponse response) {
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			UserInfo ui = this.user(request);
			Acc6ReportSummaryExpenseBean rseb = new Acc6ReportSummaryExpenseBean();
			
			String provinceIdFilter = this.getAttr(request, "provinceIdFilter");
			String budgetYearFilter = this.getAttr(request, "budgetYearFilter"); 
			String startDateFilter = this.getAttr(request, "startDateFilter");
			String endDateFilter = this.getAttr(request, "endDateFilter");
			String[] expenseTypeFilter = request.getParameterValues("expenseTypeFilter");
			String expenseTypeT = "";
			String expenseTypeC = "";
			StringBuffer sb = new StringBuffer();
			String condition = "";
			String conditionMultiCheque = "";
			String selectFilter = "";
			
			if (provinceIdFilter.equals("")) {
				provinceIdFilter = "all";
			}
			if (budgetYearFilter.equals("")) {
				budgetYearFilter = "all";
			}

			if (expenseTypeFilter != null && expenseTypeFilter.length > 0) {
				for (int i = 0 ; i < expenseTypeFilter.length ; i ++) {
					if (expenseTypeFilter[i].equals("T")) {
						expenseTypeT = "T";
					} else {
						expenseTypeC = "C";
					}
				}
			}
			
			rseb.setProvinceIdFilter(provinceIdFilter);
			rseb.setBudgetYearFilter(budgetYearFilter);
			rseb.setStartDateFilter(this.dateUtil().changeFormatDate2YYYYMMDD(startDateFilter, "/", ""));
			rseb.setEndDateFilter(this.dateUtil().changeFormatDate2YYYYMMDD(endDateFilter, "/", ""));

			if (expenseTypeFilter == null || expenseTypeFilter.length == 2 || expenseTypeFilter.length == 0) {
				rseb.setExpenseTypeFilter("all");
			} else {
				rseb.setExpenseTypeFilter(expenseTypeFilter[0]);
			}
			sb.append("DELETE FROM "+this.schema+".ACC6_REPORT_SUMMARY_EXPENSE WHERE USER_ID = '" + ui.getUserId() + "' ");
			dao.execDataSql(sb.toString());
			
			if (ConfigBundle.getValue("DEPLOY_TYPE").equals("P")) {
				startDateFilter = this.dateUtil().changeDateToDB(startDateFilter);
				endDateFilter = this.dateUtil().changeDateToDB(endDateFilter);
				condition = " AND TO_DATE(T1.EXPENSE_DATE,'DD.MM.YYYY') >= " + startDateFilter + " AND TO_DATE(T1.EXPENSE_DATE,'DD.MM.YYYY') <= " + endDateFilter + " ";
				conditionMultiCheque = " AND TO_DATE(T1.CHEQUE_DATE,'DD.MM.YYYY') >= " + startDateFilter + " AND TO_DATE(T1.CHEQUE_DATE,'DD.MM.YYYY') <= " + endDateFilter + " ";
			} else {
				startDateFilter =  this.dateUtil().changeFormatDate2YYYYMMDD(startDateFilter, "/", "");
				endDateFilter = this.dateUtil().changeFormatDate2YYYYMMDD(endDateFilter, "/", "");
				condition = " AND TO_CHAR(T1.EXPENSE_DATE,'YYYYMMDD') >= '" + startDateFilter + "' AND TO_CHAR(T1.EXPENSE_DATE,'YYYYMMDD') <= '" + endDateFilter + "' ";
				conditionMultiCheque = " AND TO_CHAR(T1.CHEQUE_DATE,'YYYYMMDD') >= '" + startDateFilter + "' AND TO_CHAR(T1.CHEQUE_DATE,'YYYYMMDD') <= '" + endDateFilter + "' ";
			}
			sb.setLength(0);
			selectFilter = " '" + ui.getUserId() + "' AS USER_ID,'" + provinceIdFilter + "' AS PROVINCE_ID_FILTER, '" + rseb.getBudgetYearFilter() + "' AS BUDGET_YEAR_FILTER  ";
			selectFilter += ",'" + rseb.getStartDateFilter() + "' AS START_DATE_FILTER ,'" + rseb.getEndDateFilter() + "' AS END_DATE_FILTER ,'" + rseb.getExpenseTypeFilter() + "' AS EXPENSE_TYPE_FILTER,";
			sb.append("INSERT INTO " + this.schema + ".ACC6_REPORT_SUMMARY_EXPENSE (USER_ID,PROVINCE_ID_FILTER,BUDGET_YEAR_FILTER,START_DATE_FILTER ");
			sb.append(",END_DATE_FILTER,EXPENSE_TYPE_FILTER,PROVINCE_ID,BUDGET_YEAR,EXPENSE_TYPE,NHSO_ZONE,EXPENSE_STATUS_P,EXPENSE_STATUS_A,EXPENSE_STATUS_W ");
			sb.append(",EXPENSE_STATUS_Y,EXPENSE_STATUS_N) ");
			sb.append("SELECT * FROM ( ");
			condition += " AND T1.ACTIVE = 'Y' ";
			conditionMultiCheque += " AND T1.ACTIVE = 'Y' ";
			if (expenseTypeFilter == null || expenseTypeT.equals("T")) {
				sb.append("SELECT DISTINCT " + selectFilter + " P.PROVINCE_ID, ACA.BUDGET_YEAR,'T' AS EXPENSE_TYPE,P.NHSO_ZONE, ");
				sb.append("(SELECT NVL(SUM(T2.TRANSFER_AMOUNT),0) AS SUM_TRANSFER_AMOUNT ");
				sb.append("FROM " + this.schema + ".ACC6_EXPENSE T1, " + this.schema + ".ACC6_EXPENSE_TRANSFER T2 WHERE T1.DOC_NO = T2.DOC_NO AND T1.EXPENSE_STATUS = 'P' ");
				sb.append("AND T1.PROVINCE_ID = P.PROVINCE_ID AND T1.BUDGET_YEAR = ACA.BUDGET_YEAR AND T1.EXPENSE_TYPE = 'T' " + condition + " ) AS EXPENSE_STATUS_P, ");
				sb.append("(SELECT NVL(SUM(T2.TRANSFER_AMOUNT),0) AS SUM_TRANSFER_AMOUNT ");
				sb.append("FROM " + this.schema + ".ACC6_EXPENSE T1, " + this.schema + ".ACC6_EXPENSE_TRANSFER T2 WHERE T1.DOC_NO = T2.DOC_NO AND T1.EXPENSE_STATUS = 'A' ");
				sb.append("AND T1.PROVINCE_ID = P.PROVINCE_ID AND T1.BUDGET_YEAR = ACA.BUDGET_YEAR AND T1.EXPENSE_TYPE = 'T' " + condition + " ) AS EXPENSE_STATUS_A, ");
				sb.append("(SELECT NVL(SUM(T2.TRANSFER_AMOUNT),0) AS SUM_TRANSFER_AMOUNT ");
				sb.append("FROM " + this.schema + ".ACC6_EXPENSE T1, " + this.schema + ".ACC6_EXPENSE_TRANSFER T2 WHERE T1.DOC_NO = T2.DOC_NO AND T1.EXPENSE_STATUS = 'W' ");
				sb.append("AND T1.PROVINCE_ID = P.PROVINCE_ID AND T1.BUDGET_YEAR = ACA.BUDGET_YEAR AND T1.EXPENSE_TYPE = 'T' " + condition + " ) AS EXPENSE_STATUS_W, ");
				sb.append("(SELECT NVL(SUM(T3.EXPENSE_AMOUNT),0) AS SUM_TRANSFER_AMOUNT ");
				sb.append("FROM " + this.schema + ".ACC6_EXPENSE T1, " + this.schema + ".ACC6_EXPENSE_TRANSFER T2, " + this.schema + ".ACC6_TRANSACTION T3 WHERE T1.DOC_NO = T2.DOC_NO AND (T1.EXPENSE_STATUS = 'Y' OR T1.EXPENSE_STATUS = 'S') ");
				sb.append("AND T1.PROVINCE_ID = P.PROVINCE_ID AND T1.BUDGET_YEAR = ACA.BUDGET_YEAR ");
				sb.append("AND T2.TRANSACTION_TIMESTAMP = T3.TRANSACTION_TIMESTAMP AND T3.REASON_CODE = '00' AND T1.EXPENSE_TYPE = 'T'  " + condition + "  ) AS EXPENSE_STATUS_Y, ");
				sb.append("(SELECT NVL(SUM(T3.EXPENSE_AMOUNT),0) AS SUM_TRANSFER_AMOUNT ");
				sb.append("FROM " + this.schema + ".ACC6_EXPENSE T1, " + this.schema + ".ACC6_EXPENSE_TRANSFER T2, " + this.schema + ".ACC6_TRANSACTION T3 WHERE T1.DOC_NO = T2.DOC_NO AND (T1.EXPENSE_STATUS = 'N' OR T1.EXPENSE_STATUS = 'S') ");
				sb.append("AND T1.PROVINCE_ID = P.PROVINCE_ID AND T1.BUDGET_YEAR = ACA.BUDGET_YEAR ");
				sb.append("AND T2.TRANSACTION_TIMESTAMP = T3.TRANSACTION_TIMESTAMP AND T3.REASON_CODE != '00'  AND T1.EXPENSE_TYPE = 'T'  " + condition + " ) AS EXPENSE_STATUS_N ");
				sb.append("FROM " + this.schema + ".ACC6_ALLOCATE ACA, " + this.schema + ".PROVINCE P ");
			}
			if (expenseTypeFilter == null || (expenseTypeC.equals("C") && expenseTypeT.equals("T"))) {
				sb.append("UNION ");
			}
			if (expenseTypeFilter == null || expenseTypeC.equals("C")) {
				condition = condition.replaceAll("T1.EXPENSE", "T2.CHEQUE");
				sb.append("SELECT DISTINCT " + selectFilter + " P.PROVINCE_ID, ACA.BUDGET_YEAR,'C' AS EXPENSE_TYPE,P.NHSO_ZONE, ");
				sb.append("(SELECT NVL(SUM(T2.CHEQUE_AMOUNT),0) AS CHEQUE_AMOUNT ");
				sb.append("FROM " + this.schema + ".ACC6_EXPENSE T1, " + this.schema + ".ACC6_EXPENSE_CHEQUE T2 WHERE T1.DOC_NO = T2.DOC_NO AND T1.EXPENSE_STATUS = 'P' ");
				sb.append("AND T1.PROVINCE_ID = P.PROVINCE_ID AND T1.BUDGET_YEAR = ACA.BUDGET_YEAR AND T1.EXPENSE_TYPE = 'C'  " + condition + " ) + ");
				sb.append("(SELECT NVL(SUM(T1.SUM_CHEQUE_AMOUNT),0) AS CHEQUE_AMOUNT ");
				sb.append("FROM " + this.schema + ".ACC6_CHEQUE T1,  " + this.schema + ".ACC6_CHEQUE_DETAIL T2 WHERE T1.DOC_NO = T2.DOC_NO AND T1.CHEQUE_STATUS = 'P' ");
				sb.append("AND T1.PROVINCE_ID = P.PROVINCE_ID AND T2.BUDGET_YEAR = ACA.BUDGET_YEAR " + conditionMultiCheque + " ) AS EXPENSE_STATUS_P,");
				sb.append("(SELECT NVL(SUM(T2.CHEQUE_AMOUNT),0) AS CHEQUE_AMOUNT ");
				sb.append("FROM " + this.schema + ".ACC6_EXPENSE T1, " + this.schema + ".ACC6_EXPENSE_CHEQUE T2 WHERE T1.DOC_NO = T2.DOC_NO AND T1.EXPENSE_STATUS = 'A' ");
				sb.append("AND T1.PROVINCE_ID = P.PROVINCE_ID AND T1.BUDGET_YEAR = ACA.BUDGET_YEAR AND T1.EXPENSE_TYPE = 'C' " + condition + "  ) + ");
				sb.append("(SELECT NVL(SUM(SUM_CHEQUE_AMOUNT),0) AS CHEQUE_AMOUNT ");
				sb.append("FROM " + this.schema + ".ACC6_CHEQUE T1,  " + this.schema + ".ACC6_CHEQUE_DETAIL T2 WHERE T1.DOC_NO = T2.DOC_NO AND T1.CHEQUE_STATUS = 'A' ");
				sb.append("AND T1.PROVINCE_ID = P.PROVINCE_ID AND T2.BUDGET_YEAR = ACA.BUDGET_YEAR " + conditionMultiCheque + " ) AS EXPENSE_STATUS_A,");
				sb.append("((SELECT NVL(SUM(T2.CHEQUE_AMOUNT),0) AS CHEQUE_AMOUNT ");
				sb.append("FROM " + this.schema + ".ACC6_EXPENSE T1, " + this.schema + ".ACC6_EXPENSE_CHEQUE T2 WHERE T1.DOC_NO = T2.DOC_NO AND T1.EXPENSE_STATUS = 'W' ");
				sb.append("AND T1.PROVINCE_ID = P.PROVINCE_ID AND T1.BUDGET_YEAR = ACA.BUDGET_YEAR AND T1.EXPENSE_TYPE = 'C'  " + condition + "  ) + ");
				sb.append("(SELECT NVL(SUM(T2.CHEQUE_AMOUNT),0) AS CHEQUE_AMOUNT ");
				sb.append("FROM " + this.schema + ".ACC6_EXPENSE T1, " + this.schema + ".ACC6_EXPENSE_CHEQUE T2 WHERE T1.DOC_NO = T2.DOC_NO AND T1.EXPENSE_STATUS = 'S' ");
				sb.append("AND T2.CHEQUE_RECEIVING_DATE IS NULL AND T1.PROVINCE_ID = P.PROVINCE_ID AND T1.BUDGET_YEAR = ACA.BUDGET_YEAR AND T1.EXPENSE_TYPE = 'C'  " + condition + " )) + ");
				sb.append("(SELECT NVL(SUM(T2.CHEQUE_AMOUNT),0) AS CHEQUE_AMOUNT ");
				sb.append("FROM " + this.schema + ".ACC6_CHEQUE T1,  " + this.schema + ".ACC6_CHEQUE_DETAIL T2 WHERE T1.DOC_NO = T2.DOC_NO AND T1.CHEQUE_STATUS = 'W' ");
				sb.append("AND T1.PROVINCE_ID = P.PROVINCE_ID AND T2.BUDGET_YEAR = ACA.BUDGET_YEAR " + conditionMultiCheque + " ) AS EXPENSE_STATUS_W,");
				sb.append("(SELECT NVL(SUM(T3.EXPENSE_AMOUNT),0) AS EXPENSE_AMOUNT ");
				sb.append("FROM " + this.schema + ".ACC6_EXPENSE T1, " + this.schema + ".ACC6_EXPENSE_CHEQUE T2, " + this.schema + ".ACC6_TRANSACTION T3 ");
				sb.append("WHERE T1.DOC_NO = T2.DOC_NO AND (T1.EXPENSE_STATUS = 'Y' OR T1.EXPENSE_STATUS = 'S') ");
				sb.append("AND T1.PROVINCE_ID = P.PROVINCE_ID AND T1.BUDGET_YEAR = ACA.BUDGET_YEAR ");
				sb.append("AND T2.TRANSACTION_TIMESTAMP = T3.TRANSACTION_TIMESTAMP AND T3.REASON_CODE = '00' ");
				sb.append("AND T1.EXPENSE_TYPE = 'C'  " + condition + "  ) + ");
				sb.append("(SELECT NVL(SUM(T3.EXPENSE_AMOUNT),0) AS EXPENSE_AMOUNT ");
				sb.append("FROM " + this.schema + ".ACC6_CHEQUE T1, " + this.schema + ".ACC6_CHEQUE_DETAIL T2, " + this.schema + ".ACC6_TRANSACTION T3 ");
				sb.append("WHERE T1.DOC_NO = T2.DOC_NO AND (T1.CHEQUE_STATUS = 'Y' OR T1.CHEQUE_STATUS = 'S') AND T1.PROVINCE_ID = P.PROVINCE_ID ");
				sb.append("AND T2.BUDGET_YEAR = ACA.BUDGET_YEAR " + conditionMultiCheque + " ");
				sb.append("AND T2.TRANSACTION_TIMESTAMP = T3.TRANSACTION_TIMESTAMP AND T3.REASON_CODE = '00'  ) AS EXPENSE_STATUS_Y,0 AS EXPENSE_STATUS_N  ");
				/*sb.append("(SELECT NVL(SUM(T3.EXPENSE_AMOUNT),0) AS SUM_TRANSFER_AMOUNT ");
				sb.append("FROM " + this.schema + ".ACC6_EXPENSE T1, " + this.schema + ".ACC6_EXPENSE_CHEQUE T2, " + this.schema + ".ACC6_TRANSACTION T3 ");
				sb.append("WHERE T1.DOC_NO = T2.DOC_NO AND (T1.EXPENSE_STATUS = 'N' OR T1.EXPENSE_STATUS = 'S') ");
				sb.append("AND T1.PROVINCE_ID = P.PROVINCE_ID AND T1.BUDGET_YEAR = ACA.BUDGET_YEAR ");
				sb.append("AND T2.TRANSACTION_TIMESTAMP = T3.TRANSACTION_TIMESTAMP AND T3.REASON_CODE IS NULL ");
				sb.append("AND T1.EXPENSE_TYPE = 'C'  " + condition + "  ) AS EXPENSE_STATUS_N ");*/
				sb.append("FROM " + this.schema + ".ACC6_ALLOCATE ACA, " + this.schema + ".PROVINCE P ");
			}
			sb.append(") ");
			sb.append("WHERE NHSO_ZONE NOT IN ('14','15') ");
			
			if (!provinceIdFilter.equals("all")) {
				sb.append("AND PROVINCE_ID = '" + provinceIdFilter + "' ");
			} else if (provinceIdFilter.equals("all") && ui.getRoleId().equals("2")) {
				sb.append("AND NHSO_ZONE = '" + ui.getAreaCode() + "' ");
			}
			
			if (!budgetYearFilter.equals("all")) {
				sb.append("AND BUDGET_YEAR = '" + budgetYearFilter + "' ");
			}
			dao.execDataSql(sb.toString());
			System.out.println("sql report 6 >> " + sb.toString());
			this.url = "http://"+this.reportHost+"/BOE/CrystalReports/viewrpt.cwr?&id="+this.idReport6+"&sRefresh=Y" +
		    "&apsuser=" + this.reportUser + "&apspassword=" + this.reportPassword + "" +
		    "&apsauthtype=" + this.reportType + "&promptex-userId=" + ui.getUserId();
			response.sendRedirect(this.url);
			
			request.setAttribute("provinceIdFilter", provinceIdFilter);
			request.setAttribute("budgetYearFilter", budgetYearFilter);
			request.setAttribute("startDateFilter", this.getAttr(request, "startDateFilter"));
			request.setAttribute("endDateFilter", this.getAttr(request, "endDateFilter"));
			request.setAttribute("expenseTypeT", expenseTypeT);
			request.setAttribute("expenseTypeC", expenseTypeC);
			request.setAttribute("lsBudgetYear", this.dataUtil(dao).lsBudgetYear());
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
				this.url = "";
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	
	private String ACC6REPORT007(HttpServletRequest request, HttpServletResponse response) {
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			String provinceId = this.getAttr(request, "provinceId");
			String budgetYear = this.getAttr(request, "allocateYear");
			String startDate = this.dateUtil().changeFormatDate2YYYYMMDD(this.getAttr(request, "startDate"), "/", "");
			String endDate = this.dateUtil().changeFormatDate2YYYYMMDD(this.getAttr(request, "endDate"), "/","");
			String chequeNo = this.getAttr(request, "chequeNo");
			String expenseStatus = this.getAttr(request, "expenseStatus");
			String allocateCode = this.getAttr(request, "allocateCode");
			//String[] cashStatus = request.getParameterValues("cashStatus");
			UserInfo ui = this.user(request);
			String areaCodeCondition = "";
			if (ui.getRoleId().equals("2")) {
				areaCodeCondition = ui.getAreaCode();
			}
			String condition = "&promptex-cheaueStatus=";
			
			/*if (cashStatus != null && cashStatus.length < 2) {
				condition = condition + cashStatus[0];
			}*/
			if (expenseStatus.equals("Y")) {
				condition = "&promptex-cheaueStatus=Y";
			} else if (expenseStatus.equals("N")) {
				condition = "&promptex-cheaueStatus=N";
			}
			
			this.url = "http://"+this.reportHost+"/BOE/CrystalReports/viewrpt.cwr?&id="+this.idReport7+"&sRefresh=Y" +
		    "&apsuser=" + this.reportUser + "&apspassword=" + this.reportPassword + "" +
		    "&apsauthtype=" + this.reportType + "&promptex-provinceId=" + provinceId + "&promptex-startDate="+startDate  +
		    "&promptex-endDate=" + endDate + "&promptex-budgetYear=" + budgetYear + "&promptex-chequeNo=" + chequeNo +
		    "&promptex-expenseStatus=" + expenseStatus + "" +condition + "&promptex-allocateCode=" + allocateCode + "&promptex-nhsoZone=" + areaCodeCondition;
			response.sendRedirect(this.url);
			
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
				this.url = "";
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	
	private String ACC6REPORT008(HttpServletRequest request, HttpServletResponse response) {
		try {
			String provinceId = this.getAttr(request, "provinceId");
			String budgetYear = this.getAttr(request, "budgetYear");
			String startDate = this.getAttr(request, "startDate");
			String endDate = this.getAttr(request, "endDate");
			String type = this.getAttr(request, "type");
			String transactionType = this.getAttr(request, "transactionType");
			String expenseStatus = this.getAttr(request, "expenseStatus");
			startDate = this.dateUtil().changeFormatDate2YYYYMMDD(startDate, "/", "");
			endDate = this.dateUtil().changeFormatDate2YYYYMMDD(endDate, "/", "");
			UserInfo ui = this.user(request);
			String reportId = "";
			String condition = "";
			String areaCodeCondition = "";
			if (ui.getRoleId().equals("2")) {
				areaCodeCondition = ui.getAreaCode();
			}
			
			if (type.equals("E")) {
				reportId = this.idReport81;
				condition = "&promptex-expendseType=" + transactionType + "&promptex-expenseStatus=" + expenseStatus;
			} else {
				reportId = this.idReport82;
				condition = "&promptex-incomeType=" + transactionType + "&promptex-incomeStatus=" + expenseStatus;
			}

			this.url = "http://"+this.reportHost+"/BOE/CrystalReports/viewrpt.cwr?&id="+reportId+"&sRefresh=Y" +
		    "&apsuser=" + this.reportUser + "&apspassword=" + this.reportPassword + "" +
		    "&apsauthtype=" + this.reportType + "&promptex-provinceId=" + provinceId + "&promptex-startDate="+startDate  +
		    "&promptex-endDate=" + endDate + "&promptex-budgetYear=" + budgetYear + condition + "&promptex-nhsoZone=" + areaCodeCondition;
			
			System.out.println(" By FreeAlife  report 02 : " + this.url);
			
			response.sendRedirect(this.url);
			
		} catch (IOException e) {
			
			System.out.println(" [ Error ] " + e.toString());
			
			e.printStackTrace();
			
		}
		this.url = "";
		return this.url;
	}
	
	private String ACC6REPORT009(HttpServletRequest request, HttpServletResponse response) {
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			String provinceId = this.getAttr(request, "provinceId");
			String budgetYear = this.getAttr(request, "budgetYear");
			String startDate = this.dateUtil().changeFormatDate2YYYYMMDD(this.getAttr(request, "startDate"), "/", "");
			String endDate = this.dateUtil().changeFormatDate2YYYYMMDD(this.getAttr(request, "endDate"), "/","");
			String categoryId = this.getAttr(request, "categoryId");
			String expenseType = this.getAttr(request, "expenseType");
			UserInfo ui = this.user(request);
			String condition = "";
			if (ui.getRoleId().equals("2")) {
				condition = ui.getAreaCode();
			}
			
			this.url = "http://"+this.reportHost+"/BOE/CrystalReports/viewrpt.cwr?&id="+this.idReport9+"&sRefresh=Y" +
		    "&apsuser=" + this.reportUser + "&apspassword=" + this.reportPassword + "" +
		    "&apsauthtype=" + this.reportType + "&promptex-provinceId=" + provinceId + "&promptex-startDate="+startDate  +
		    "&promptex-endDate=" + endDate + "&promptex-bugetYear=" + budgetYear + "&promptex-categoryId=" + categoryId +
		    "&promptex-expenseType=" + expenseType + "&promptex-nhsoZone=" + condition;
			response.sendRedirect(this.url);
			
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
				this.url = "";
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	
	private String ACC6REPORT010(HttpServletRequest request, HttpServletResponse response) {
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			UserInfo ui = this.user(request);
			String provinceId = this.getAttr(request, "provinceId");
			String allocateYear = this.getAttr(request, "allocateYear");
			String startDate = this.dateUtil().changeFormatDate2YYYYMMDD(this.getAttr(request, "startDate"), "/", "");
			String endDate = this.dateUtil().changeFormatDate2YYYYMMDD(this.getAttr(request, "endDate"), "/","");
			String allocateCode = this.getAttr(request, "allocateCode");
			String condition = "";
			if (ui.getRoleId().equals("2")) {
				condition = ui.getAreaCode();
			}
			this.url = "http://"+this.reportHost+"/BOE/CrystalReports/viewrpt.cwr?&id="+this.idReport10+"&sRefresh=Y" +
		    "&apsuser=" + this.reportUser + "&apspassword=" + this.reportPassword + "" +
		    "&apsauthtype=" + this.reportType + "&promptex-provinceId=" + provinceId + "&promptex-startDate="+startDate  +
		    "&promptex-endDate=" + endDate + "&promptex-budgetYear=" + allocateYear + "&promptex-allocateCode=" + allocateCode +
		    "&promptex-nhsoZone=" + condition;
			response.sendRedirect(this.url);
			
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
				this.url = "";
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	
	private String ACC6REPORT011(HttpServletRequest request, HttpServletResponse response) {
		
		Connection conn = null;
		ISUD dao = null;
		
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			
			// Parameter 
			String provinceId = this.getAttr(request, "provinceId");
			String budgetYear = this.getAttr(request, "budgetYear");
			String startDate = this.getAttr(request, "startDate");
			String endDate = this.getAttr(request, "endDate");
			
			StringBuffer sb = new StringBuffer();
			String startDateCondition = "";
			String endDateCondition = "";
			List listResult = new ArrayList();
		
			// Date formate 
			startDateCondition =  this.dateUtil().changeFormatDate2YYYYMMDD(startDate, "/", "");
			endDateCondition = this.dateUtil().changeFormatDate2YYYYMMDD(endDate, "/", "");
			
			sb.append("SELECT ");
			sb.append(" PV.NHSO_ZONE,PV.PROVINCE_ID,PV.PROVINCE_NAME,ACCALL.BUDGET_YEAR,");
			sb.append(" CAST(ACCALL.ALLOCATE_CODE AS VARCHAR(8)) AS ALLOCATE_CODE, ACCALL.ALLOCATE_NAME   ,    ");
  
			//-- INCOME AMOUNT FIRST YEAR
			//-- ตรง ACTRN.BUDGET_YEAR ต้องให้ Program เปลี่ยนแปลง value budget year.
			sb.append(" CASE WHEN ACCALL.BUDGET_YEAR = '" + budgetYear + "' THEN");
    		sb.append(" SUM(CASE WHEN ACTRN.TRANSACTION_TYPE = 'B' AND ACTRN.REASON_CODE = '00' AND ACTRN.ALLOCATE_CODE = ACCALL.ALLOCATE_CODE ");
			sb.append(" AND ACTRN.PROVINCE_ID = PV.PROVINCE_ID AND ACTRN.BUDGET_YEAR = ACCALL.BUDGET_YEAR  ");
			// -- AND TO_DATE(INCOME_DATE,'DD.MM.YYYY') >= '20131001' AND TO_DATE(INCOME_DATE,'DD.MM.YYYY') <= '20140731'
			sb.append(" AND TO_CHAR(INCOME_DATE,'YYYYMMDD') >= '" + startDateCondition + "' AND TO_CHAR(INCOME_DATE,'YYYYMMDD') <= '" + endDateCondition + "' ");
			sb.append(" THEN ACTRN.INCOME_AMOUNT ELSE 0 END ) ");  
    		sb.append(" ELSE 0 END AS INCOME_B, "); // End dat type B กรณีที่มีการแสดงข้อมูล Type ต้องเป็น ปี 2557 เท่านั้น
    
    		sb.append(" SUM( ");
	        sb.append(" CASE WHEN ACTRN.TRANSACTION_TYPE IN  ('S','R','O','F')  AND ACTRN.REASON_CODE = '00' ");
	        sb.append(" AND ACTRN.ALLOCATE_CODE = ACCALL.ALLOCATE_CODE  AND ACTRN.PROVINCE_ID = PV.PROVINCE_ID ");
	        sb.append(" AND ACTRN.BUDGET_YEAR = '" + budgetYear + "'   AND TO_CHAR(INCOME_DATE,'YYYYMMDD') >= '"+ startDateCondition + "' ");
	        sb.append(" AND TO_CHAR(INCOME_DATE,'YYYYMMDD') <= '"+endDateCondition +"'  THEN ACTRN.INCOME_AMOUNT ELSE 0 END ");
	        sb.append(" ) AS INCOME_ONE , "); // เป็นกลุ่มรายได้ปีแรก
	        
	        // -- INCOME AMOUNT SECORN YEAR
	        // -- ตรง ACTRN.BUDGET_YEAR ต้องให้ Program เปลี่ยนแปลง value budget year.
	        sb.append(" SUM( ");
	        sb.append(" CASE WHEN ACTRN.TRANSACTION_TYPE IN  ('S','R','O','F')  AND ACTRN.REASON_CODE = '00' ");
	        sb.append(" AND ACTRN.ALLOCATE_CODE = ACCALL.ALLOCATE_CODE  AND ACTRN.PROVINCE_ID = PV.PROVINCE_ID ");
	        sb.append(" AND ACTRN.BUDGET_YEAR = '" + String.valueOf((Integer.parseInt(budgetYear) - 1)) + "'   AND TO_CHAR(INCOME_DATE,'YYYYMMDD') >= '" + startDateCondition + "' ");
	        sb.append(" AND TO_CHAR(INCOME_DATE,'YYYYMMDD') <= '" + endDateCondition + "'  THEN ACTRN.INCOME_AMOUNT ELSE 0 END ");
	        sb.append(" ) AS INCOME_TWO ,");
	        
	        // -- INCOME AMOUNT THREE YEAR 
	        // -- ตรง ACTRN.BUDGET_YEAR ต้องให้ Program เปลี่ยนแปลง value budget year.
	        sb.append(" SUM( ");
	        sb.append(" CASE WHEN ACTRN.TRANSACTION_TYPE IN  ('S','R','O','F')  AND ACTRN.REASON_CODE = '00' ");
	        sb.append(" AND ACTRN.ALLOCATE_CODE = ACCALL.ALLOCATE_CODE  AND ACTRN.PROVINCE_ID = PV.PROVINCE_ID ");
	        sb.append(" AND ACTRN.BUDGET_YEAR <= '"+ String.valueOf((Integer.parseInt(budgetYear) - 2)) +"'   AND TO_CHAR(INCOME_DATE,'YYYYMMDD') >= '20000000' "); // -- นับจำนวนทั้งหมด จำได้กำหนดให้เป็นแบบนี้
	        sb.append("  AND TO_CHAR(INCOME_DATE,'YYYYMMDD') <= '" + endDateCondition + "'  THEN ACTRN.INCOME_AMOUNT ELSE 0 END "); 
	   	   	sb.append(" ) AS INCOME_THREE , ");
    
	   	   	// -- รายจ่าย
		   	sb.append(" ( ");
		   	sb.append(" SUM(CASE WHEN ACTRN.TRANSACTION_TYPE IN ('C') AND ACTRN.REASON_CODE = '00' AND ACTRN.ALLOCATE_CODE = ACCALL.ALLOCATE_CODE ");
		   	sb.append(" AND ACTRN.PROVINCE_ID = PV.PROVINCE_ID");
		   	//-- AND ACTRN.BUDGET_YEAR =  '2557'   
		   	sb.append(" AND TO_CHAR(CHEQUE_DATE,'YYYYMMDD') >= '" + startDateCondition+ "' AND TO_CHAR(CHEQUE_DATE,'YYYYMMDD') <= '" + endDateCondition + "'  ");
		   	sb.append(" THEN ACTRN.EXPENSE_AMOUNT ELSE 0 END )  ");
		   	sb.append(" + ");
		   	sb.append(" SUM(CASE WHEN ACTRN.TRANSACTION_TYPE IN ('T' , 'P') AND ACTRN.REASON_CODE = '00' AND ACTRN.ALLOCATE_CODE = ACCALL.ALLOCATE_CODE ");
		   	sb.append(" AND ACTRN.PROVINCE_ID = PV.PROVINCE_ID ");
		   	//-- AND ACTRN.BUDGET_YEAR =  '2557' 
		   	sb.append(" AND TO_CHAR(EXPENSE_DATE,'YYYYMMDD') >= '" + startDateCondition + "' AND TO_CHAR(EXPENSE_DATE,'YYYYMMDD') <= '" + endDateCondition + "'  ");
		   	sb.append(" THEN ACTRN.EXPENSE_AMOUNT ELSE 0 END ) ");
		   	sb.append(" ) AS EXPENST_ONE ");
		   	
		   	sb.append(" FROM ACC6_ALLOCATE ACCALL ");
		   	sb.append(" INNER JOIN ACC6_TRANSACTION ACTRN  ON ACCALL.ALLOCATE_CODE = ACTRN.ALLOCATE_CODE AND ACCALL.BUDGET_YEAR  =  ACTRN.BUDGET_YEAR ");
		   	sb.append(" INNER JOIN PROVINCE PV ON ACTRN.PROVINCE_ID = PV.PROVINCE_ID ");
		   	sb.append(" WHERE 1 > 0 ");
		   	
		   	if(!provinceId.equals("")){ 
		   		sb.append("AND  PV.PROVINCE_ID  = '" + provinceId + "' ");
		   	}
		   	
		   	sb.append(" GROUP BY  PV.NHSO_ZONE , PV.PROVINCE_ID , PV.PROVINCE_NAME , ACCALL.BUDGET_YEAR ,CAST(ACCALL.ALLOCATE_CODE AS VARCHAR(8)), ACCALL.ALLOCATE_NAME ");
		   	sb.append(" ORDER BY ACCALL.BUDGET_YEAR DESC  , CAST(ACCALL.ALLOCATE_CODE AS VARCHAR(8)) ");
			
		   	System.out.println(sb.toString());
		   	
		   listResult = dao.listDataSql(sb.toString());
	   		 
			request.setAttribute("provinceId", provinceId);
			request.setAttribute("budgetYear", budgetYear);
			request.setAttribute("startDate", this.getAttr(request, "startDate"));
			request.setAttribute("endDate", this.getAttr(request, "endDate"));
			
			request.setAttribute("listResult", listResult);
			request.setAttribute("lsBudgetYear", this.dataUtil(dao).lsBudgetYear());
			
			conn.commit();
		
		} catch (Exception e) {
			
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
		
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
				this.url = "/apps/acc6report/ACC6REPORT011.jsp";
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}

	private String ACC6REPORT012(HttpServletRequest request, HttpServletResponse response) {
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			PageBean pageBean = new PageBean();
			String condition = "";
			StringBuffer sb = new StringBuffer();
			String transferDailyAcc = this.getAttr(request, "transferDailyAcc");
			String accountType = this.getAttr(request, "accountType");
			String provinceId = this.getAttr(request, "provinceId");
			String showResult = this.getAttr(request, "showResult");
			String startDate = this.getAttr(request, "startDate");
			String endDate = this.getAttr(request, "endDate");
			String month = this.getAttr(request, "month");
			String year =  this.getAttr(request, "year");
			
			this.beanUtil().copyRequest2Bean(request, pageBean);
			
			if (provinceId != null && !provinceId.equals("")) {
				condition = " AND PROVINCE_ID = '" + provinceId + "' ";
			}

			if (showResult.equals("2")) {
				pageBean.setOrderField("T2.PROVINCE_NAME || T2.TRANSFER_DAILY_KEY");
				sb.append(" SELECT T2.* FROM ");
				sb.append(" (SELECT MAX(TRANSFER_DAILY_KEY) AS TRANSFER_DAILY_KEY,PROVINCE_ID FROM " + this.schema + ".ACC67_TRANSFER_DAILY ");
				sb.append(" WHERE SUBSTR(TRANSFER_DATE,5,2) = '" + month + "'"); 
				
				if(!year.equals("")){
					sb.append(" AND SUBSTR(TRANSFER_DATE,0,4) = '" + year + "'");
				}
				
				sb.append(" AND TRANSFER_DAILY_ACC = '"+transferDailyAcc+"' AND ACCOUNT_TYPE = '" + accountType + "'  GROUP BY PROVINCE_ID) ");
				sb.append(" T1," + this.schema + ".ACC67_TRANSFER_DAILY T2 WHERE T1.TRANSFER_DAILY_KEY = T2.TRANSFER_DAILY_KEY ");
				sb.append(" AND T2.TRANSFER_DAILY_ACC = '"+transferDailyAcc+"' AND T2.ACCOUNT_TYPE = '" + accountType + "' " + condition.replaceAll("PROVINCE_ID", "T2.PROVINCE_ID"));
			
			} else {
			
				pageBean.setOrderField("PROVINCE_NAME || TRANSFER_DAILY_KEY");
				startDate = this.dateUtil().changeFormatDate2YYYYMMDD(startDate, "/", "");
				endDate = this.dateUtil().changeFormatDate2YYYYMMDD(endDate, "/", "");
				sb.append("SELECT * FROM " + this.schema + ".ACC67_TRANSFER_DAILY ");
				sb.append("WHERE TRANSFER_DAILY_ACC = '"+transferDailyAcc+"' AND ACCOUNT_TYPE = '" + accountType + "' " + condition);
				sb.append(" AND TO_NUMBER(TRANSFER_DATE) >= TO_NUMBER(" + startDate + ") AND TO_NUMBER(TRANSFER_DATE) <= TO_NUMBER(" + endDate + ") ");
				
			}
			
			pageBean.setOrderType("ASC");
			
			// show  statement  SQl 
			System.out.println("1797  >> sb.toString() >> " + sb.toString());
			
			pageBean = dao.selectPage(sb.toString(), pageBean);
			if (pageBean.getPageData() == null) {
				this.message(request).setMsgCode("I001");
			}
			
			request.setAttribute("pageBean", pageBean);
			request.setAttribute("transferDailyAcc", transferDailyAcc);
			request.setAttribute("accountType", accountType);
			request.setAttribute("provinceId", provinceId);
			request.setAttribute("showResult", showResult);
			request.setAttribute("startDate", this.getAttr(request, "startDate"));
			request.setAttribute("endDate", this.getAttr(request, "endDate"));
			request.setAttribute("lsBudgetYear", this.dataUtil(dao).lsBudgetYear());
			request.setAttribute("year" , year);
			
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
			dao.printMessage();
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
				this.url = "/apps/acc6report/ACC6REPORT012.jsp";
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	
	private void ACC6REPORT005SaveData(ISUD dao,String conditionIncomeDate, String conditionExpendDate, String budgetYear, String provinceId, Acc6ReportMoneyStatusBean amsb, String allocYearFilter, String budgetYearFilter) throws Exception{
		Map map = new HashMap();
		StringBuffer sb = new StringBuffer();
		int iBudgetYear = Integer.parseInt(budgetYear);
		int iBudgetYearAc = Integer.parseInt(budgetYear) - 543;
		int iBudgetYearFilter = Integer.parseInt(budgetYearFilter);
		double incomeAmount = 0;
		double expenseAmount = 0;
		double remain = 0;
		//Status 'บันทึก'
		amsb.setBudgetYear(budgetYear);
		amsb.setProvinceId(provinceId);
		amsb.setStatusId("1");
		amsb.setStatus("บันทึก");
		// get Income Amount type B
		sb.setLength(0);
		sb.append("SELECT NVL(SUM (BALANCE_AMOUNT),0) AS BALANCE_AMOUNT FROM " + this.schema + ".ACC6_BUDGET_BALANCE ");
		sb.append("WHERE BALANCE_STATUS = 'P' AND BUDGET_YEAR = '" + budgetYear + "' AND PROVINCE_ID = '" + provinceId + "' ");
		sb.append("AND ACTIVE = 'Y' ");
		map = dao.findDataSql(sb.toString());
		if (allocYearFilter.equals("all")) {
			amsb.setIncomeAmountTypeB("0");
		} else {
			if (iBudgetYear <= 2556) {
				amsb.setIncomeAmountTypeB(dao.chkData(map.get("BALANCE_AMOUNT")));
			} else {
				amsb.setIncomeAmountTypeB("0");
			}
		}
		// get Income Amount type S
		sb.setLength(0);
		sb.append("SELECT NVL(SUM (INCOME_AMOUNT),0) AS INCOME_AMOUNT FROM " + this.schema + ".ACC6_TRANSACTION ");
		sb.append("WHERE TRANSACTION_TYPE = 'S' AND BUDGET_YEAR = '" + budgetYear + "' " + conditionIncomeDate + " ");
		sb.append("AND PROVINCE_ID = '" + provinceId + "' AND REASON_CODE = '00' ");
		map = dao.findDataSql(sb.toString());
		amsb.setIncomeAmountTypeS(dao.chkData(map.get("INCOME_AMOUNT")));
		// get Income Amount type R,O,F
		sb.setLength(0);
		sb.append("SELECT NVL(SUM (INCOME_AMOUNT),0) AS INCOME_AMOUNT FROM " + this.schema + ".ACC6_INCOME ");
		sb.append("WHERE INCOME_STATUS = 'P' AND BUDGET_YEAR = '" + budgetYear + "' AND PROVINCE_ID = '" + provinceId + "' " + conditionIncomeDate + " ");
		sb.append("AND ACTIVE = 'Y' ");
		map = dao.findDataSql(sb.toString());
		amsb.setIncomeAmountTypeO(dao.chkData(map.get("INCOME_AMOUNT")));
		// get Expense Amount type P
		sb.setLength(0);
		sb.append("SELECT NVL(SUM (EXPENSE_AMOUNT),0) AS EXPENSE_AMOUNT FROM " + this.schema + ".ACC6_EXPENSE_OTHER ");
		sb.append("WHERE EXPENSE_STATUS = 'P' AND BUDGET_YEAR = '" + budgetYear + "' AND PROVINCE_ID = '" + provinceId + "' " + conditionExpendDate + " ");
		sb.append("AND ACTIVE = 'Y' ");
		map = dao.findDataSql(sb.toString());
		amsb.setExpenseAmountTypeP(dao.chkData(map.get("EXPENSE_AMOUNT")));
		dao.insert(amsb);
		amsb.setStatus("");
		amsb.setIncomeAmountTypeB("");
		amsb.setIncomeAmountTypeS("");
		amsb.setIncomeAmountTypeO("");
		amsb.setExpenseAmountTypeP("");
		
		//Status 'ยืนยัน'
		amsb.setStatus("ยืนยัน");
		amsb.setStatusId("2");
		// get Income Amount type B
		sb.setLength(0);

		String condition = "";
		String conditionIncomeDate2 = "";
		String conditionExpendDate2 = "";
		if (ConfigBundle.getValue("DEPLOY_TYPE").equals("P")) {
			if (iBudgetYear <= 2556) {
				conditionIncomeDate2 = " AND TO_DATE(INCOME_DATE,'DD.MM.YYYY') >= TO_DATE('01/10/2012','DD.MM.YYYY') AND TO_DATE(INCOME_DATE,'DD.MM.YYYY') <= TO_DATE('30/09/2013','DD.MM.YYYY') ";
				conditionExpendDate2 = " AND TO_DATE(EXPENSE_DATE,'DD.MM.YYYY') >= TO_DATE('01/10/2012','DD.MM.YYYY') AND TO_DATE(EXPENSE_DATE,'DD.MM.YYYY') <= TO_DATE('30/09/2013','DD.MM.YYYY') ";
			} else {
				conditionIncomeDate2 = " AND TO_DATE(INCOME_DATE,'DD.MM.YYYY') >= TO_DATE('01/10/2012" + (iBudgetYearAc - 2) + "','DD.MM.YYYY') AND TO_DATE(INCOME_DATE,'DD.MM.YYYY') <= TO_DATE('30/09/" + (iBudgetYearAc - 1) + "','DD.MM.YYYY') ";
				conditionExpendDate2 = " AND TO_DATE(EXPENSE_DATE,'DD.MM.YYYY') >= TO_DATE('01/10/" + (iBudgetYearAc - 2) + "','DD.MM.YYYY') AND TO_DATE(EXPENSE_DATE,'DD.MM.YYYY') <= TO_DATE('30/09/" + (iBudgetYearAc - 1) + "','DD.MM.YYYY') ";
			}
		} else {
			if (iBudgetYear <= 2556) {
				conditionIncomeDate2 = " AND TO_CHAR(INCOME_DATE,'YYYYMMDD') >= '20121001' AND TO_CHAR(INCOME_DATE,'YYYYMMDD') <= '20130930' ";
				conditionExpendDate2 = " AND TO_CHAR(EXPENSE_DATE,'YYYYMMDD') >= '20121001' AND TO_CHAR(EXPENSE_DATE,'YYYYMMDD') <= '20130930' ";
			} else {
				conditionIncomeDate2 = " AND TO_CHAR(INCOME_DATE,'YYYYMMDD') >= '" + (iBudgetYearAc - 2) + "1001' AND TO_CHAR(INCOME_DATE,'YYYYMMDD') <= '" + (iBudgetYearAc - 1) + "0930' ";
				conditionExpendDate2 = " AND TO_CHAR(EXPENSE_DATE,'YYYYMMDD') >= '" + (iBudgetYearAc - 2) + "1001' AND TO_CHAR(EXPENSE_DATE,'YYYYMMDD') <= '" + (iBudgetYearAc - 1) + "0930' ";
			}
		}
		if (iBudgetYear <= 2556) {
			condition = " AND BUDGET_YEAR = '" + budgetYear + "' ";
		} else {
			condition = " AND BUDGET_YEAR = '" + (iBudgetYear - 1) + "' ";
		}
		
		if (allocYearFilter.equals("all")) {
			if (iBudgetYear <= 2556) {
				if (iBudgetYearFilter <= 2556) {
					sb.setLength(0);
					sb.append("SELECT NVL(SUM (BALANCE_AMOUNT),0) AS BALANCE_AMOUNT FROM " + this.schema + ".ACC6_BUDGET_BALANCE ");
					sb.append("WHERE BALANCE_STATUS = 'Y' AND BUDGET_YEAR = '" + budgetYear + "' AND PROVINCE_ID = '" + provinceId + "' ");
					sb.append("AND ACTIVE = 'Y' ");
					map = dao.findDataSql(sb.toString());
					amsb.setIncomeAmountTypeB(dao.chkData(map.get("BALANCE_AMOUNT")));
				} else {
					sb.setLength(0);
					sb.append("SELECT NVL(SUM(INCOME_AMOUNT),0)AS INCOME_AMOUNT FROM " + this.schema + ".ACC6_TRANSACTION ");
					sb.append("WHERE TRANSACTION_TYPE IN ('S','B','R','O','F') AND PROVINCE_ID = '" + provinceId + "' AND REASON_CODE = '00' " + condition + conditionIncomeDate2);
					map = dao.findDataSql(sb.toString());
					incomeAmount = Double.parseDouble(dao.chkData(map.get("INCOME_AMOUNT")));
					
					sb.setLength(0);
					sb.append("SELECT NVL(SUM(EXPENSE_AMOUNT),0) AS EXPENSE_AMOUNT FROM " + this.schema + ".ACC6_TRANSACTION ");
					sb.append("WHERE TRANSACTION_TYPE IN ('T','P') AND PROVINCE_ID = '" + provinceId + "' AND REASON_CODE = '00' " + condition + conditionExpendDate2);
					map = dao.findDataSql(sb.toString());
					expenseAmount = Double.parseDouble(dao.chkData(map.get("EXPENSE_AMOUNT")));
					
					sb.setLength(0);
					sb.append("SELECT NVL(SUM(EXPENSE_AMOUNT),0) AS EXPENSE_AMOUNT FROM " + this.schema + ".ACC6_TRANSACTION ");
					sb.append("WHERE TRANSACTION_TYPE IN ('C') AND PROVINCE_ID = '" + provinceId + "' AND REASON_CODE = '00' " + condition + conditionExpendDate2.replaceAll("EXPENSE", "CHEQUE"));
					map = dao.findDataSql(sb.toString());
					expenseAmount += Double.parseDouble(dao.chkData(map.get("EXPENSE_AMOUNT")));
					remain = incomeAmount - expenseAmount;
					amsb.setIncomeAmountTypeB(dao.chkData(remain));
				}
			} else {
				amsb.setIncomeAmountTypeB("0.00");
			}
		} else {
			if (iBudgetYear <= 2556) {
				if (iBudgetYearFilter <= 2556) {
					sb.setLength(0);
					sb.append("SELECT NVL(SUM (BALANCE_AMOUNT),0) AS BALANCE_AMOUNT FROM " + this.schema + ".ACC6_BUDGET_BALANCE ");
					sb.append("WHERE BALANCE_STATUS = 'Y' AND BUDGET_YEAR = '" + budgetYear + "' AND PROVINCE_ID = '" + provinceId + "' ");
					sb.append("AND ACTIVE = 'Y' ");
					map = dao.findDataSql(sb.toString());
					amsb.setIncomeAmountTypeB(dao.chkData(map.get("BALANCE_AMOUNT")));
				} else {
					/*sb.setLength(0);
					sb.append("SELECT NVL(SUM(INCOME_AMOUNT),0) AS INCOME_AMOUNT FROM " + this.schema + ".ACC6_TRANSACTION ");
					sb.append("WHERE TRANSACTION_TYPE IN ('S','B','R','O','F') AND PROVINCE_ID = '" + provinceId + "' AND REASON_CODE = '00' " + conditionIncomeDate2);
					sb.append("AND BUDGET_YEAR = '" + iBudgetYear + "' ");
					map = dao.findDataSql(sb.toString());
					incomeAmount = Double.parseDouble(dao.chkData(map.get("INCOME_AMOUNT")));
					
					sb.setLength(0);
					sb.append("SELECT NVL(SUM(EXPENSE_AMOUNT),0) AS EXPENSE_AMOUNT FROM " + this.schema + ".ACC6_TRANSACTION ");
					sb.append("WHERE TRANSACTION_TYPE IN ('T','P') AND PROVINCE_ID = '" + provinceId + "' AND REASON_CODE = '00' " + conditionExpendDate2);
					sb.append("AND BUDGET_YEAR = '" + iBudgetYear + "' ");
					map = dao.findDataSql(sb.toString());
					expenseAmount = Double.parseDouble(dao.chkData(map.get("EXPENSE_AMOUNT")));
					
					sb.setLength(0);
					sb.append("SELECT NVL(SUM(EXPENSE_AMOUNT),0) AS EXPENSE_AMOUNT FROM " + this.schema + ".ACC6_TRANSACTION ");
					sb.append("WHERE TRANSACTION_TYPE IN ('C') AND PROVINCE_ID = '" + provinceId + "' AND REASON_CODE = '00' " + conditionExpendDate2.replaceAll("EXPENSE", "CHEQUE"));
					sb.append("AND BUDGET_YEAR = '" + iBudgetYear + "' ");
					map = dao.findDataSql(sb.toString());
					expenseAmount += Double.parseDouble(dao.chkData(map.get("EXPENSE_AMOUNT")));
					remain = incomeAmount - expenseAmount;
					amsb.setIncomeAmountTypeB(dao.chkData(remain));*/
					amsb.setIncomeAmountTypeB("0.00");
				}
			} else {
				/*sb.setLength(0);
				sb.append("SELECT NVL(SUM(INCOME_AMOUNT),0) AS INCOME_AMOUNT FROM " + this.schema + ".ACC6_TRANSACTION ");
				sb.append("WHERE TRANSACTION_TYPE IN ('S','B','R','O','F') AND PROVINCE_ID = '" + provinceId + "' AND REASON_CODE = '00' " + conditionIncomeDate);
				sb.append("AND BUDGET_YEAR = '" + (iBudgetYear - 1) + "' ");
				map = dao.findDataSql(sb.toString());
				incomeAmount = Double.parseDouble(dao.chkData(map.get("INCOME_AMOUNT")));
				
				sb.setLength(0);
				sb.append("SELECT NVL(SUM(EXPENSE_AMOUNT),0) AS EXPENSE_AMOUNT FROM " + this.schema + ".ACC6_TRANSACTION ");
				sb.append("WHERE TRANSACTION_TYPE IN ('T','P') AND PROVINCE_ID = '" + provinceId + "' AND REASON_CODE = '00' " + conditionExpendDate);
				sb.append("AND BUDGET_YEAR = '" + (iBudgetYear - 1) + "' ");
				map = dao.findDataSql(sb.toString());
				expenseAmount = Double.parseDouble(dao.chkData(map.get("EXPENSE_AMOUNT")));
				
				sb.setLength(0);
				sb.append("SELECT NVL(SUM(EXPENSE_AMOUNT),0) AS EXPENSE_AMOUNT FROM " + this.schema + ".ACC6_TRANSACTION ");
				sb.append("WHERE TRANSACTION_TYPE IN ('C') AND PROVINCE_ID = '" + provinceId + "' AND REASON_CODE = '00' " + conditionExpendDate.replaceAll("EXPENSE", "CHEQUE"));
				sb.append("AND BUDGET_YEAR = '" + (iBudgetYear - 1) + "' ");
				map = dao.findDataSql(sb.toString());
				expenseAmount += Double.parseDouble(dao.chkData(map.get("EXPENSE_AMOUNT")));
				remain = incomeAmount - expenseAmount;
				amsb.setIncomeAmountTypeB(dao.chkData(remain));*/
				amsb.setIncomeAmountTypeB("0.00");
			}
		}
		// get Income Amount type R,O,F
		sb.setLength(0);
		sb.append("SELECT NVL(SUM (INCOME_AMOUNT),0) AS INCOME_AMOUNT FROM " + this.schema + ".ACC6_INCOME ");
		sb.append("WHERE INCOME_STATUS = 'Y' AND BUDGET_YEAR = '" + budgetYear + "' AND PROVINCE_ID = '" + provinceId + "' ");
		sb.append(" " + conditionIncomeDate + " AND ACTIVE = 'Y' ");
		map = dao.findDataSql(sb.toString());
		amsb.setIncomeAmountTypeO(dao.chkData(map.get("INCOME_AMOUNT")));
		// get Expense Amount type P 
		sb.setLength(0);
		sb.append("SELECT NVL(SUM (EXPENSE_AMOUNT),0) AS EXPENSE_AMOUNT FROM " + this.schema + ".ACC6_EXPENSE_OTHER ");
		sb.append("WHERE EXPENSE_STATUS = 'Y' AND BUDGET_YEAR = '" + budgetYear + "' AND PROVINCE_ID = '" + provinceId + "' ");
		sb.append(" " + conditionExpendDate + " AND ACTIVE = 'Y' ");
		map = dao.findDataSql(sb.toString());
		amsb.setExpenseAmountTypeP(dao.chkData(map.get("EXPENSE_AMOUNT")));
		dao.insert(amsb);
		amsb.setStatus("");
		amsb.setIncomeAmountTypeB("");
		amsb.setIncomeAmountTypeO("");
		amsb.setExpenseAmountTypeP("");

		//Expense
		Map mapStatusExpense = this.properties().statusExpense();
		Iterator it = mapStatusExpense.entrySet().iterator();
		String reasonCode = "";
		double sumCheckAmount = 0;
		int statusId = 3;
		while (it.hasNext()) {
			Map.Entry pairs = (Map.Entry)it.next();
			if (!pairs.getKey().toString().equals("S")) {
				amsb.setStatusId(String.valueOf(statusId));
				amsb.setStatus(pairs.getValue().toString());
				if (!pairs.getKey().toString().equals("N") && !pairs.getKey().toString().equals("Y")) {
					sumCheckAmount = 0;
					if (pairs.getKey().toString().equals("W")) {// Expense Status W
						// get Expense Amount type C
						sb.setLength(0);
						sb.append("SELECT NVL(SUM(T2.CHEQUE_AMOUNT),0)  AS CHEQUE_AMOUNT ");
						sb.append("FROM " + this.schema + ".ACC6_EXPENSE T1, " + this.schema + ".ACC6_EXPENSE_CHEQUE T2, " + this.schema + ".ACC6_TRANSACTION T3 ");
						sb.append("WHERE T1.DOC_NO = T2.DOC_NO AND (T1.EXPENSE_STATUS = 'W' OR T1.EXPENSE_STATUS = 'S') ");
						sb.append("AND T1.BUDGET_YEAR = '" + budgetYear + "' AND T1.PROVINCE_ID = '" + provinceId + "' AND T1.EXPENSE_TYPE = 'C' AND T1.ACTIVE = 'Y' ");
						sb.append(" " + conditionExpendDate.replaceAll("EXPENSE", "T2.CHEQUE") + " ");
						sb.append("AND T2.TRANSACTION_TIMESTAMP = T3.TRANSACTION_TIMESTAMP AND T3.REASON_CODE IS NULL ");
						map = dao.findDataSql(sb.toString());
						sumCheckAmount = Double.parseDouble(dao.chkData(map.get("CHEQUE_AMOUNT")));
						sb.setLength(0);
						sb.append("SELECT NVL(SUM(T2.CHEQUE_AMOUNT),0) AS CHEQUE_AMOUNT ");
						sb.append("FROM " + this.schema + ".ACC6_CHEQUE T1, " + this.schema + ".ACC6_CHEQUE_DETAIL T2, " + this.schema + ".ACC6_TRANSACTION T3 ");
						sb.append("WHERE T1.DOC_NO = T2.DOC_NO AND (T1.CHEQUE_STATUS = 'W' OR T1.CHEQUE_STATUS = 'S') ");
						sb.append("AND T2.BUDGET_YEAR = '" + budgetYear + "' AND T1.PROVINCE_ID = '" + provinceId + "' AND T1.ACTIVE = 'Y' ");
						sb.append(" " + conditionExpendDate.replaceAll("EXPENSE", "T1.CHEQUE") + " ");
						sb.append("AND T2.TRANSACTION_TIMESTAMP = T3.TRANSACTION_TIMESTAMP AND T3.REASON_CODE IS NULL ");
						map = dao.findDataSql(sb.toString());
						sumCheckAmount += Double.parseDouble(dao.chkData(map.get("CHEQUE_AMOUNT")));
					} else { // Expense Status P,A
						// get Expense Amount type C
						sb.setLength(0);
						sb.append("SELECT NVL(SUM(T2.CHEQUE_AMOUNT),0)  AS CHEQUE_AMOUNT FROM " + this.schema + ".ACC6_EXPENSE T1, " + this.schema + ".ACC6_EXPENSE_CHEQUE T2 ");
						sb.append("WHERE T1.DOC_NO = T2.DOC_NO AND T1.BUDGET_YEAR = '" + budgetYear + "' AND T1.PROVINCE_ID = '" + provinceId + "' ");
						sb.append(" " + conditionExpendDate.replaceAll("EXPENSE", "T2.CHEQUE") + " AND T1.EXPENSE_STATUS = '" + pairs.getKey().toString() + "' AND T1.EXPENSE_TYPE = 'C' AND T1.ACTIVE = 'Y'");
						map = dao.findDataSql(sb.toString());
						sumCheckAmount = Double.parseDouble(dao.chkData(map.get("CHEQUE_AMOUNT")));
						sb.setLength(0);
						sb.append("SELECT NVL(SUM(T2.CHEQUE_AMOUNT),0) AS CHEQUE_AMOUNT FROM " + this.schema + ".ACC6_CHEQUE T1, " + this.schema + ".ACC6_CHEQUE_DETAIL T2 ");
						sb.append("WHERE T1.PROVINCE_ID = '" + provinceId + "' ");
						sb.append(" " + conditionExpendDate.replaceAll("EXPENSE", "T1.CHEQUE") + " AND T2.BUDGET_YEAR = '" + budgetYear + "' ");
						sb.append("AND T1.DOC_NO = T2.DOC_NO  AND T1.CHEQUE_STATUS = '" + pairs.getKey().toString() + "' AND T1.ACTIVE = 'Y'");
						map = dao.findDataSql(sb.toString());
						sumCheckAmount += Double.parseDouble(dao.chkData(map.get("CHEQUE_AMOUNT")));
					}
					// get Expense Amount type T
					sb.setLength(0);
					sb.append("SELECT NVL(SUM(T2.TRANSFER_AMOUNT),0) AS TRANSFER_AMOUNT  FROM " + this.schema + ".ACC6_EXPENSE T1, " + this.schema + ".ACC6_EXPENSE_TRANSFER T2 ");
					sb.append("WHERE T1.DOC_NO = T2.DOC_NO AND T1.BUDGET_YEAR = '" + budgetYear + "' AND T1.PROVINCE_ID = '" + provinceId + "' ");
					sb.append(" " + conditionExpendDate.replaceAll("EXPENSE", "T1.EXPENSE") + " AND T1.EXPENSE_STATUS = '" + pairs.getKey().toString() + "' AND T1.EXPENSE_TYPE = 'T' AND T1.ACTIVE = 'Y'");
					map = dao.findDataSql(sb.toString());
					amsb.setExpenseAmountTypeT(dao.chkData(map.get("TRANSFER_AMOUNT")));
					amsb.setExpenseAmountTypeC(String.valueOf(sumCheckAmount));
					dao.insert(amsb);
				} else {
					if (pairs.getKey().toString().equals("N")) {
						reasonCode = " != '00' ";
						amsb.setExpenseAmountTypeC("");
					} else {
						reasonCode = " = '00' ";
						sb.setLength(0);
						sb.append("SELECT NVL(SUM(T2.CHEQUE_AMOUNT),0)  AS CHEQUE_AMOUNT ");
						sb.append("FROM "+this.schema+".ACC6_EXPENSE T1, "+this.schema+".ACC6_EXPENSE_CHEQUE T2, "+this.schema+".ACC6_TRANSACTION T3 ");
						sb.append("WHERE T1.DOC_NO = T2.DOC_NO AND (T1.EXPENSE_STATUS = '"+pairs.getKey().toString()+"' OR T1.EXPENSE_STATUS = 'S') ");
						sb.append("AND T1.BUDGET_YEAR = '" + budgetYear + "' AND T1.PROVINCE_ID = '" + provinceId + "' AND T1.EXPENSE_TYPE = 'C' AND T1.ACTIVE = 'Y'");
						sb.append(" " + conditionExpendDate.replaceAll("EXPENSE", "T2.CHEQUE") + " ");
						sb.append("AND T2.TRANSACTION_TIMESTAMP = T3.TRANSACTION_TIMESTAMP AND T3.REASON_CODE " + reasonCode + "");
						//System.out.println("sb.toString() Line 1817 >> " + sb.toString());
						map = dao.findDataSql(sb.toString());
						sumCheckAmount = Double.parseDouble(dao.chkData(map.get("CHEQUE_AMOUNT")));

						sb.setLength(0);
						sb.append("SELECT NVL(SUM(T2.CHEQUE_AMOUNT),0) AS CHEQUE_AMOUNT ");
						sb.append("FROM "+this.schema+".ACC6_CHEQUE T1, "+this.schema+".ACC6_CHEQUE_DETAIL T2, "+this.schema+".ACC6_TRANSACTION T3 ");
						sb.append("WHERE T1.DOC_NO = T2.DOC_NO AND (T1.CHEQUE_STATUS = 'Y' OR T1.CHEQUE_STATUS = 'S') ");
						sb.append("AND T1.PROVINCE_ID = '" + provinceId + "' AND T2.BUDGET_YEAR = '" + budgetYear + "' AND T1.ACTIVE = 'Y'");
						sb.append(" " + conditionExpendDate.replaceAll("EXPENSE", "T1.CHEQUE") + " ");
						sb.append("AND T2.TRANSACTION_TIMESTAMP = T3.TRANSACTION_TIMESTAMP AND T3.REASON_CODE " + reasonCode + "");
						//System.out.println("sb.toString() Line 1828 >> " + sb.toString());
						map = dao.findDataSql(sb.toString());
						sumCheckAmount += Double.parseDouble(dao.chkData(map.get("CHEQUE_AMOUNT")));
						amsb.setExpenseAmountTypeC(String.valueOf(sumCheckAmount));
					}
					sb.setLength(0);
					sb.append("SELECT NVL(SUM(EXPENSE_AMOUNT),0) AS EXPENSE_AMOUNT ");
					sb.append("FROM "+this.schema+".ACC6_EXPENSE T1, "+this.schema+".ACC6_EXPENSE_TRANSFER T2, "+this.schema+".ACC6_TRANSACTION T3 ");
					sb.append("WHERE T1.DOC_NO = T2.DOC_NO AND (T1.EXPENSE_STATUS = '"+pairs.getKey().toString()+"' OR T1.EXPENSE_STATUS = 'S') ");
					sb.append("AND T1.BUDGET_YEAR = '" + budgetYear + "' AND T1.PROVINCE_ID = '" + provinceId + "' AND T1.EXPENSE_TYPE = 'T' AND T1.ACTIVE = 'Y' ");
					sb.append(" " + conditionExpendDate.replaceAll("EXPENSE", "T1.EXPENSE") + " ");
					sb.append("AND T2.TRANSACTION_TIMESTAMP = T3.TRANSACTION_TIMESTAMP AND T3.REASON_CODE " + reasonCode + "");
					map = dao.findDataSql(sb.toString());
					amsb.setExpenseAmountTypeT(dao.chkData(map.get("EXPENSE_AMOUNT")));
					dao.insert(amsb);
				}
				amsb.setStatus("");
				amsb.setExpenseAmountTypeT("");
				amsb.setExpenseAmountTypeC("");
				statusId++;
			}
		}
		dao.printMessage();
	}
	
	private String ACC6REPORT001Test(HttpServletRequest request, HttpServletResponse response) {
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			List ls = new ArrayList();
			UserInfo ui = this.user(request);
			Acc6ReportOverviewBean arob = new Acc6ReportOverviewBean();
			
			String provinceIdFilter = this.getAttr(request, "provinceIdFilter");
			String budgetYearFilter = this.getAttr(request, "budgetYearFilter"); 
			String startDate = this.getAttr(request, "startDateFilter");
			String endDate = this.getAttr(request, "endDateFilter");
			String condition = ""; //for budget Year <= 2556
			String condition2 = ""; // for budget Year > 2556
			//int startBudgetYear = 2;
			//int endBudgetYear = 1;
			StringBuffer sb = new StringBuffer();

			if (provinceIdFilter.equals("")) {
				provinceIdFilter = "all";
			}
			
			arob.setProvinceIdFilter(provinceIdFilter);
			arob.setStartDateFilter(this.dateUtil().changeFormatDate2YYYYMMDD(startDate,"/",""));
			arob.setEndDateFilter(this.dateUtil().changeFormatDate2YYYYMMDD(endDate,"/",""));
			arob.setUserId(ui.getUserId());
			sb.append("DELETE FROM "+this.schema+".ACC6_REPORT_OVERVIEW WHERE USER_ID = '" + ui.getUserId() + "' ");
			dao.execDataSql(sb.toString());
			condition = ""; // for budget Year <= 2556
			condition2 = ""; // for budget Year > 2556

			// Check Deploy On Product Or Development.
			if (ConfigBundle.getValue("DEPLOY_TYPE").equals("P")) {
				startDate = this.dateUtil().changeDateToDB(startDate);
				endDate = this.dateUtil().changeDateToDB(endDate);
			} else {
				startDate =  this.dateUtil().changeFormatDate2YYYYMMDD(startDate, "/", "");
				endDate = this.dateUtil().changeFormatDate2YYYYMMDD(endDate, "/", "");
			}
			
			int iBudgetYearFilter = Integer.parseInt(budgetYearFilter);
			ls = new ArrayList();
			ls = dao.listDataSql("SELECT DISTINCT BUDGET_YEAR FROM "+this.schema+".ACC6_ALLOCATE WHERE BUDGET_YEAR <= "+budgetYearFilter+" ORDER BY BUDGET_YEAR DESC");
			
			for (int i = 0 ; i < ls.size() ; i++) {
				
				Map map = (Map)ls.get(i);
				String budgetYear = dao.chkData(map.get("BUDGET_YEAR"));
				
				int iBudgetYear = Integer.parseInt(dao.chkData(map.get("BUDGET_YEAR")));
				
				//int budgetYearAc = iBudgetYear - 543;
				for (int x = 0 ; x < 2 ; x++) {
					
					if (ConfigBundle.getValue("DEPLOY_TYPE").equals("P")) {
						condition = " AND TO_DATE(INCOME_DATE,'DD.MM.YYYY') >= " + startDate + " AND TO_DATE(INCOME_DATE,'DD.MM.YYYY') <= " + endDate + " ";
						condition2 = " AND TO_DATE(INCOME_DATE,'DD.MM.YYYY') >= TO_DATE('01/10/2012','DD.MM.YYYY') AND TO_DATE(INCOME_DATE,'DD.MM.YYYY') <= TO_DATE('30/09/2013','DD.MM.YYYY') ";
					} else {
						condition = " AND TO_CHAR(INCOME_DATE,'YYYYMMDD') >= " + startDate + " AND TO_CHAR(INCOME_DATE,'YYYYMMDD') <= " + endDate + " ";
						condition2 = " AND TO_CHAR(INCOME_DATE,'YYYYMMDD') >= '20121001' AND TO_CHAR(INCOME_DATE,'YYYYMMDD') <= '20130930' ";
					}
					
					sb.setLength(0);
					sb.append("INSERT INTO " + this.schema + ".ACC6_REPORT_OVERVIEW (USER_ID,PROVINCE_ID_FILTER,BUDGET_YEAR_FILTER,START_DATE_FILTER,");
					sb.append("END_DATE_FILTER,PROVINCE_ID,PROVINCE_NAME,ALLOCATE_CODE,ALLOCATE_NAME,SUM_INCOME_AMOUNT_TYPE_B,SUM_INCOME_AMOUNT_TYPE_S,SUM_EXPENSE_AMOUNT) ");
					sb.append("SELECT '" + ui.getUserId() + "','" + arob.getProvinceIdFilter() + "','" + budgetYear + "' AS BUDGET_YEAR," + arob.getStartDateFilter() + "," + arob.getEndDateFilter() + "");
					sb.append(",T2.PROVINCE_ID,T2.PROVINCE_NAME,T1.ALLOCATE_CODE,T1.ALLOCATE_NAME, ");
					
					if (iBudgetYear <= 2556) {
						
						if (iBudgetYearFilter <= 2556) {
							sb.append("CASE WHEN LENGTH(T1.ALLOCATE_CODE) = 4 AND T2.PROVINCE_ID IN (SELECT PROVINCE_ID FROM ACC6_TRANSACTION) ");
							sb.append("THEN(SELECT NVL(SUM(INCOME_AMOUNT),0.00) AS INCOME_AMOUNT ");
							sb.append("FROM " + this.schema + ".ACC6_TRANSACTION ");
							sb.append("WHERE TRANSACTION_TYPE = 'B' AND PROVINCE_ID = T2.PROVINCE_ID ");
							sb.append("AND ALLOCATE_CODE LIKE T1.ALLOCATE_CODE || '%' ");
							sb.append("AND BUDGET_YEAR = T1.BUDGET_YEAR AND REASON_CODE = '00' ) ");
							sb.append("WHEN LENGTH(T1.ALLOCATE_CODE) > 4 AND T2.PROVINCE_ID IN (SELECT PROVINCE_ID FROM " + this.schema + ".ACC6_TRANSACTION) ");
							sb.append("THEN(SELECT NVL(SUM(INCOME_AMOUNT),0.00) AS INCOME_AMOUNT ");
							sb.append("FROM " + this.schema + ".ACC6_TRANSACTION ");
							sb.append("WHERE TRANSACTION_TYPE = 'B' AND PROVINCE_ID = T2.PROVINCE_ID ");
							sb.append("AND ALLOCATE_CODE = T1.ALLOCATE_CODE ");
							sb.append("AND BUDGET_YEAR = T1.BUDGET_YEAR AND REASON_CODE = '00' ) ");
							sb.append("ELSE 0.00 END AS INCOME_AMOUNT_TYPE_B, ");
						} else {
							sb.append("CASE WHEN LENGTH(T1.ALLOCATE_CODE) = 4 AND T2.PROVINCE_ID IN (SELECT PROVINCE_ID FROM " + this.schema + ".ACC6_TRANSACTION) ");
							sb.append("THEN(SELECT NVL(SUM(INCOME_AMOUNT),0.00) AS INCOME_AMOUNT FROM " + this.schema + ".ACC6_TRANSACTION WHERE TRANSACTION_TYPE IN ('B','S','F','R','O') ");
							sb.append("AND PROVINCE_ID = T2.PROVINCE_ID AND ALLOCATE_CODE LIKE SUBSTR('" + (iBudgetYear) + "',3,2) || SUBSTR(T1.ALLOCATE_CODE,3,2) || '%' ");
							sb.append("AND REASON_CODE = '00' " + condition2 + " ) - ");
							sb.append("((SELECT NVL(SUM(EXPENSE_AMOUNT),0.00) AS EXPENSE_AMOUNT FROM " + this.schema + ".ACC6_TRANSACTION WHERE TRANSACTION_TYPE IN ('T','P') AND PROVINCE_ID = T2.PROVINCE_ID ");
							sb.append("AND ALLOCATE_CODE LIKE SUBSTR('" + (iBudgetYear) + "',3,2) || SUBSTR(T1.ALLOCATE_CODE,3,2) || '%' ");
							sb.append("AND REASON_CODE = '00' " + condition2.replaceAll("INCOME_DATE", "EXPENSE_DATE") + ") +  ");
							sb.append("(SELECT NVL(SUM(EXPENSE_AMOUNT),0.00) AS EXPENSE_AMOUNT FROM "+this.schema+".ACC6_TRANSACTION  WHERE TRANSACTION_TYPE IN ('C') ");
							sb.append("AND PROVINCE_ID = T2.PROVINCE_ID AND ALLOCATE_CODE LIKE SUBSTR(" + (iBudgetYear) + ",3,2) || SUBSTR(T1.ALLOCATE_CODE,3,2) || '%' ");
							sb.append("AND REASON_CODE = '00' " + condition2.replaceAll("INCOME_DATE", "CHEQUE_DATE") + " ");
							sb.append("))");
							sb.append("WHEN LENGTH(T1.ALLOCATE_CODE) > 4 AND T2.PROVINCE_ID IN (SELECT PROVINCE_ID FROM " + this.schema + ".ACC6_TRANSACTION) ");
							sb.append("THEN(SELECT NVL(SUM(INCOME_AMOUNT),0.00) AS INCOME_AMOUNT FROM " + this.schema + ".ACC6_TRANSACTION WHERE TRANSACTION_TYPE IN ('B','S','F','R','O') ");
							sb.append("AND PROVINCE_ID = T2.PROVINCE_ID AND ALLOCATE_CODE LIKE SUBSTR('" + (iBudgetYear) + "',3,2) || SUBSTR(T1.ALLOCATE_CODE,3,2) || '%' ");
							sb.append("AND REASON_CODE = '00' " + condition2 +" ) - ");
							sb.append("((SELECT NVL(SUM(EXPENSE_AMOUNT),0.00) AS EXPENSE_AMOUNT FROM " + this.schema + ".ACC6_TRANSACTION WHERE TRANSACTION_TYPE IN ('T','P') AND PROVINCE_ID = T2.PROVINCE_ID ");
							sb.append("AND ALLOCATE_CODE LIKE SUBSTR('" + (iBudgetYear) + "',3,2) || SUBSTR(T1.ALLOCATE_CODE,3,2) || '%' ");
							sb.append("AND REASON_CODE = '00' " + condition2.replaceAll("INCOME_DATE", "EXPENSE_DATE") + ") +  ");
							sb.append("(SELECT NVL(SUM(EXPENSE_AMOUNT),0.00) AS EXPENSE_AMOUNT FROM " + this.schema + ".ACC6_TRANSACTION  WHERE TRANSACTION_TYPE IN ('C') ");
							sb.append("AND PROVINCE_ID = T2.PROVINCE_ID AND ALLOCATE_CODE LIKE SUBSTR(" + (iBudgetYear) + ",3,2) || SUBSTR(T1.ALLOCATE_CODE,3,2) || '%' ");
							sb.append("AND REASON_CODE = '00' " + condition2.replaceAll("INCOME_DATE", "CHEQUE_DATE") + " ");
							sb.append("))");
							sb.append("ELSE 0.00 END AS INCOME_AMOUNT_TYPE_B, ");
						}
						
					} else {
						
						/*sb.append("CASE WHEN LENGTH(T1.ALLOCATE_CODE) = 4 AND T2.PROVINCE_ID IN (SELECT PROVINCE_ID FROM " + this.schema + ".ACC6_TRANSACTION) ");
						sb.append("THEN(SELECT NVL(SUM(INCOME_AMOUNT),0.00) AS INCOME_AMOUNT FROM " + this.schema + ".ACC6_TRANSACTION WHERE TRANSACTION_TYPE IN ('B','S','F','R','O') ");
						sb.append("AND PROVINCE_ID = T2.PROVINCE_ID AND ALLOCATE_CODE LIKE SUBSTR('" + (iBudgetYear-1) + "',3,2) || SUBSTR(T1.ALLOCATE_CODE,3,2) || '%' ");
						sb.append("AND REASON_CODE = '00' " + condition2 + " ) - ");
						sb.append("((SELECT NVL(SUM(EXPENSE_AMOUNT),0.00) AS EXPENSE_AMOUNT FROM " + this.schema + ".ACC6_TRANSACTION WHERE TRANSACTION_TYPE IN ('T','P') AND PROVINCE_ID = T2.PROVINCE_ID ");
						sb.append("AND ALLOCATE_CODE LIKE SUBSTR('" + (iBudgetYear-1) + "',3,2) || SUBSTR(T1.ALLOCATE_CODE,3,2) || '%' ");
						sb.append("AND REASON_CODE = '00' " + condition2.replaceAll("INCOME_DATE", "EXPENSE_DATE") + ") +  ");
						sb.append("(SELECT NVL(SUM(EXPENSE_AMOUNT),0.00) AS EXPENSE_AMOUNT FROM "+this.schema+".ACC6_TRANSACTION  WHERE TRANSACTION_TYPE IN ('C') ");
						sb.append("AND PROVINCE_ID = T2.PROVINCE_ID AND ALLOCATE_CODE LIKE SUBSTR(" + (iBudgetYear-1) + ",3,2) || SUBSTR(T1.ALLOCATE_CODE,3,2) || '%' ");
						sb.append("AND REASON_CODE = '00' " + condition2.replaceAll("INCOME_DATE", "CHEQUE_DATE") + " ");
						sb.append("))");
						sb.append("WHEN LENGTH(T1.ALLOCATE_CODE) > 4 AND T2.PROVINCE_ID IN (SELECT PROVINCE_ID FROM " + this.schema + ".ACC6_TRANSACTION) ");
						sb.append("THEN(SELECT NVL(SUM(INCOME_AMOUNT),0.00) AS INCOME_AMOUNT FROM " + this.schema + ".ACC6_TRANSACTION WHERE TRANSACTION_TYPE IN ('B','S','F','R','O') ");
						sb.append("AND PROVINCE_ID = T2.PROVINCE_ID AND ALLOCATE_CODE LIKE SUBSTR('" + (iBudgetYear-1) + "',3,2) || SUBSTR(T1.ALLOCATE_CODE,3,2) || '%' ");
						sb.append("AND REASON_CODE = '00' " + condition2 +" ) - ");
						sb.append("((SELECT NVL(SUM(EXPENSE_AMOUNT),0.00) AS EXPENSE_AMOUNT FROM " + this.schema + ".ACC6_TRANSACTION WHERE TRANSACTION_TYPE IN ('T','P') AND PROVINCE_ID = T2.PROVINCE_ID ");
						sb.append("AND ALLOCATE_CODE LIKE SUBSTR('" + (iBudgetYear-1) + "',3,2) || SUBSTR(T1.ALLOCATE_CODE,3,2) || '%' ");
						sb.append("AND REASON_CODE = '00' " + condition2.replaceAll("INCOME_DATE", "EXPENSE_DATE") + ") +  ");
						sb.append("(SELECT NVL(SUM(EXPENSE_AMOUNT),0.00) AS EXPENSE_AMOUNT FROM " + this.schema + ".ACC6_TRANSACTION  WHERE TRANSACTION_TYPE IN ('C') ");
						sb.append("AND PROVINCE_ID = T2.PROVINCE_ID AND ALLOCATE_CODE LIKE SUBSTR(" + (iBudgetYear-1) + ",3,2) || SUBSTR(T1.ALLOCATE_CODE,3,2) || '%' ");
						sb.append("AND REASON_CODE = '00' " + condition2.replaceAll("INCOME_DATE", "CHEQUE_DATE") + " ");
						sb.append("))");
						sb.append("ELSE 0.00 END AS INCOME_AMOUNT_TYPE_B, ");*/
						sb.append(" 0.00 AS INCOME_AMOUNT_TYPE_B, ");
					}
					sb.append("CASE WHEN LENGTH(T1.ALLOCATE_CODE) = 4 AND  T2.PROVINCE_ID IN (SELECT PROVINCE_ID FROM " + this.schema + ".ACC6_TRANSACTION) ");
					sb.append("THEN(SELECT NVL(SUM(INCOME_AMOUNT),0.00) AS INCOME_AMOUNT ");
					sb.append("FROM " + this.schema + ".ACC6_TRANSACTION ");
					sb.append("WHERE TRANSACTION_TYPE IN ('S','F','R','O') AND PROVINCE_ID = T2.PROVINCE_ID ");
					sb.append("AND ALLOCATE_CODE LIKE T1.ALLOCATE_CODE || '%' ");
					sb.append("AND BUDGET_YEAR = T1.BUDGET_YEAR AND REASON_CODE = '00' " + condition + " ) ");
					sb.append("WHEN LENGTH(T1.ALLOCATE_CODE) > 4 AND T2.PROVINCE_ID IN (SELECT PROVINCE_ID FROM " + this.schema + ".ACC6_TRANSACTION) ");
					sb.append("THEN(SELECT NVL(SUM(INCOME_AMOUNT),0.00) AS INCOME_AMOUNT ");
					sb.append("FROM " + this.schema + ".ACC6_TRANSACTION ");
					sb.append("WHERE TRANSACTION_TYPE IN ('S','F','R','O') AND PROVINCE_ID = T2.PROVINCE_ID ");
					sb.append("AND ALLOCATE_CODE = T1.ALLOCATE_CODE ");
					sb.append("AND BUDGET_YEAR = T1.BUDGET_YEAR AND REASON_CODE = '00' " + condition + " ) ");
					sb.append("ELSE 0.00 END AS INCOME_AMOUNT_TYPE_S, ");
					sb.append("CASE WHEN LENGTH(T1.ALLOCATE_CODE) = 4 AND T2.PROVINCE_ID IN (SELECT PROVINCE_ID FROM " + this.schema + ".ACC6_TRANSACTION) ");
					sb.append("THEN(SELECT NVL(SUM(EXPENSE_AMOUNT),0.00) AS EXPENSE_AMOUNT ");
					sb.append("FROM " + this.schema + ".ACC6_TRANSACTION ");
					sb.append("WHERE TRANSACTION_TYPE IN ('T','P') AND PROVINCE_ID = T2.PROVINCE_ID ");
					sb.append("AND ALLOCATE_CODE LIKE T1.ALLOCATE_CODE || '%' ");
					
					if (ConfigBundle.getValue("DEPLOY_TYPE").equals("P")) {
						condition = " AND TO_DATE(EXPENSE_DATE,'DD.MM.YYYY') >= " + startDate + " AND TO_DATE(EXPENSE_DATE,'DD.MM.YYYY') <= " + endDate + " ";
					} else {
						condition = " AND TO_CHAR(EXPENSE_DATE,'YYYYMMDD') >= " + startDate + " AND TO_CHAR(EXPENSE_DATE,'YYYYMMDD') <= " + endDate + " ";
					}
					
					sb.append("AND BUDGET_YEAR = T1.BUDGET_YEAR AND REASON_CODE = '00' " + condition + " ) + ");
					sb.append("(SELECT NVL(SUM(EXPENSE_AMOUNT),0.00) AS EXPENSE_AMOUNT ");
					sb.append("FROM " + this.schema + ".ACC6_TRANSACTION ");
					sb.append("WHERE TRANSACTION_TYPE IN ('C') AND PROVINCE_ID = T2.PROVINCE_ID ");
					sb.append("AND ALLOCATE_CODE LIKE T1.ALLOCATE_CODE || '%' ");
					sb.append("AND BUDGET_YEAR = T1.BUDGET_YEAR AND REASON_CODE = '00' " + condition.replaceAll("EXPENSE", "CHEQUE") + " ) ");
					sb.append("WHEN LENGTH(T1.ALLOCATE_CODE) > 4 AND T2.PROVINCE_ID IN (SELECT PROVINCE_ID FROM " + this.schema + ".ACC6_TRANSACTION) ");
					sb.append("THEN(SELECT NVL(SUM(EXPENSE_AMOUNT),0.00) AS EXPENSE_AMOUNT ");
					sb.append("FROM " + this.schema + ".ACC6_TRANSACTION ");
					sb.append("WHERE TRANSACTION_TYPE IN ('T','P') AND PROVINCE_ID = T2.PROVINCE_ID ");
					sb.append("AND ALLOCATE_CODE = T1.ALLOCATE_CODE ");
					sb.append("AND BUDGET_YEAR = T1.BUDGET_YEAR AND REASON_CODE = '00' " + condition + " ) + ");
					sb.append("(SELECT NVL(SUM(EXPENSE_AMOUNT),0.00) AS EXPENSE_AMOUNT ");
					sb.append("FROM " + this.schema + ".ACC6_TRANSACTION ");
					sb.append("WHERE TRANSACTION_TYPE IN ('C') AND PROVINCE_ID = T2.PROVINCE_ID ");
					sb.append("AND ALLOCATE_CODE = T1.ALLOCATE_CODE ");
					sb.append("AND BUDGET_YEAR = T1.BUDGET_YEAR AND REASON_CODE = '00' " + condition.replaceAll("EXPENSE", "CHEQUE") + " )");
					sb.append("ELSE 0.00 END AS EXPENSE_AMOUNT ");
					sb.append("FROM " + this.schema + ".ACC6_ALLOCATE T1, PROVINCE T2 ");
					sb.append("WHERE ");
					if (x == 0) {
						sb.append("((LENGTH(T1.ALLOCATE_CODE)) = 4 AND T1.ALLOCATE_LEVEL != 0 ");
						sb.append("AND ((SUBSTR(T1.ALLOCATE_CODE, 3, 2) != '77') ");
						sb.append("AND (SUBSTR(T1.ALLOCATE_CODE, 3, 2) != '88') ");
						sb.append("AND (SUBSTR(T1.ALLOCATE_CODE, 3, 2) != '99'))) ");
					} else {
						sb.append("(LENGTH(T1.ALLOCATE_CODE)) = 8 AND T1.ALLOCATE_LEVEL != 0  ");
						sb.append("AND (T1.ALLOCATE_CODE = '"+budgetYear.substring(2, 4)+"777777' ");
						sb.append("OR T1.ALLOCATE_CODE = '"+budgetYear.substring(2, 4)+"888888' ");
						sb.append("OR T1.ALLOCATE_CODE = '"+budgetYear.substring(2, 4)+"999999') ");
					}
					sb.append(" AND (T2.NHSO_ZONE != 13 AND T2.NHSO_ZONE != 14) AND T1.BUDGET_YEAR = '" + budgetYear + "' ");
					if (!provinceIdFilter.equals("all") || ui.getRoleId().equals("3")){	
						sb.append(" AND T2.PROVINCE_ID = '" + provinceIdFilter + "' ");
					} else if (ui.getRoleId().equals("2") && provinceIdFilter.equals("all")) {
						sb.append(" AND T2.NHSO_ZONE = '" + ui.getAreaCode() + "' ");
					}
					System.out.println("Report 01 >> " + sb.toString() );
					dao.execDataSql(sb.toString());
				}
				
				//System.out.println("report 10 : " + sb.toString());
			}

			this.url = "http://"+this.reportHost+"/BOE/CrystalReports/viewrpt.cwr?&id="+this.idReport3+"&sRefresh=Y" +
		    "&apsuser=" + this.reportUser + "&apspassword=" + this.reportPassword + "" +
		    "&apsauthtype=" + this.reportType + "&promptex-userId=" + ui.getUserId();
			response.sendRedirect(this.url);
					
			request.setAttribute("budgetYearFilter", budgetYearFilter);
			request.setAttribute("provinceIdFilter", provinceIdFilter);
			request.setAttribute("lsBudgetYear", this.dataUtil(dao).lsBudgetYear());
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			e.printStackTrace();
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
				this.url = "";
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	
	
}
